CODEWITHSUNDEEP
excelvbafind
Kevin Kim
Kevin Kim

Reputation: 81

Range.find method to find cells with specific colorindex

I want to find cells with the same colorindex with a range So I recorded a macro to find cells with a color and applied to its code like this. But it doesn't work.

How can I find cells with a specific colorindex?

For Each r In rngC
lcolorID = r.Offset(, 1).Interior.ColorIndex
     With wsD.UsedRange
          With Application.FindFormat.Interior
         .PatternColorIndex = xlAutomatic
          .Color = lcolorID
          End With
      Set c = .Find(What:="", After:=ActiveCell,  _ 
LookIn:=xlFormulas, LookAt:= xlPart, SearchOrder:=xlByRows, _ 
SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False, _ 
SearchFormat:=True)

        If Not c Is Nothing Then
        Debug.Print c.Address
        End If
   End With
Next r

Upvotes: 0

Views: 2073

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11191

Try Application.FindFormat.Clear before With Application.FindFormat.Interior.

You may have some formatting already set which may prevent you in matching desired cells.

Upvotes: 1

Diveye
Diveye

Reputation: 271

If Cell.Interior.ColorIndex = yourColorIndex Then 'using your color variable 
    'your actions here
End If

Place the above if condition in a for loop where you go through all the rows and it should work! And replace "Cell" by the specific cell you are currently handling in the project using the loop you are using to go through your sheet. If your sheet name is "Sheet" then use:

Sheets("Sheet").Cell(rowindex, columnindex).Interior.ColorIndex

Where rowindex and columnindex are the indexes your are looping through.

Upvotes: 1

Related Questions