L
L
lid2pro2018-10-28 21:22:02
Macros
lid2pro, 2018-10-28 21:22:02

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 question

Ask a Question

731 491 924 answers to any question