Finding cells with certain interior color

Question

I'm using this code to locate cells that are a certain color (changed by conditional formatting), then based on that values from the row are swapped to another sheet. However, the macro runs and just doesn't find anything. No error message, it's just not locating any cells (that I've switched to RGB 255, 0, 0 for this test). What am I doing wrong here?

Sub ChangeAccountDetailsForMay()

Dim ws As Worksheet
Set ws = Sheets("comparison")
Dim destws As Worksheet
Set destws = Sheets("Account Detail")
Dim i As Integer
Dim j As Integer

For i = 24 To 3205
    If ActiveWorkbook.Sheets("comparison").Range("BF" & i).Interior.Color = RGB(255, 0, 0) Then 'Might be RGB 218 148 148
        MsgBox "Found one at row " & i & "!"
        For j = 25 To 3077
            If ActiveWorkbook.Sheets(destws).Range("J" & j).Value = ActiveWorkbook.Sheets(ws).Range("J" & i).Value And ActiveWorkbook.Sheets(destws).Range("L" & j).Value = ActiveWorkbook.Sheets(ws).Range("L" & i).Value Then
                ActiveWorkbook.Sheets(destws).Range("BD" & j).Value = ActiveWorkbook.Sheets(ws).Range("BB" & i).Value
                ActiveWorkbook.Sheets(destws).Range("BE" & j).Value = ActiveWorkbook.Sheets(ws).Range("BC" & i).Value
                ActiveWorkbook.Sheets(destws).Range("BF" & j).Value = ActiveWorkbook.Sheets(ws).Range("BD" & i).Value
            End If
        Next j
    End If
Next i

MsgBox "Done!", vbInformation, "Success!"
End Sub

Use .DisplayFormat: ...Range ("BF" & i).DisplayFormat.Interior.Color... — Scott Craner
@ScottCraner it is a valid answer. You may want to post it for future visitors? — Siddharth Rout

Scott Craner Scott Craner · Accepted Answer · 2017-06-02T19:41:46

To get the color of the cell that is provided from Conditional Formatting one must use DisplayFormat

...Range ("BF" & i).DisplayFormat.Interior.Color...

Finding cells with certain interior color

1 Answers