Answer the question
In order to leave comments, you need to log in
Excel search value throughout the book depending on the color?
Good afternoon,
there is an excel book, where there are a lot of sheets and in the sheets there is a record of customers.
There is a Verification sheet, where in A1 we enter the full name and use a macro to search throughout the book where this full name occurs. The fact is that in the sheets the names are painted by color. How to make it so that the search was only in green colors? How to include it in the check? not strong in VBA, macro code below
Sub Ca()
Dim w As Worksheet, s$, a$, c As Range, d As Range
For Each c In Range("A2", Cells(Rows.Count, "A").End(xlUp))
s = ""
For Each w In Worksheets
If Not w Is ActiveSheet Then
Set d = w.Cells.Find(c, , xlValues, xlWhole)
If Not d Is Nothing Then
a = d.Address
Do
s = s & ", " & w.Name & " - " & w.Cells(1, d.Column)
Set d = w.Cells.FindNext(d)
Loop Until d.Address = a
End If
End If
Next
c.Offset(, 1) = Mid(s, 3)
Next
End Sub
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question