Get address of cell with conditional formatting in VBA

[qnguyen]

I have in column A various strings, and a rule that highlights certain ones in red.

Is there an easy way to get the reference of the first cell that has been highlighted? For example, if the cell on row 53 is highlighted, I want to get "A53"

I tried FormatConditions.Count but that only tells me how many cells there are in the column. I can also loop through each cell with the formatting rule, but that kind of defeats the purpose.

Thanks!

 

[jim may]

Are you using a formula within your conditional formatting set up window? If so, what is the formulatext?

 

[Jaafar Tribak]

Assuming the interior highlight color is red, you could use the DisplayFormat Property... Something along these lines:

If Range("A53").DisplayFormat.Interior.Color = vbRed Then

    MsgBox "A53 highlighted"


End If

 

[mikerickson]

Perhaps something like

Dim oneCondition As FormatCondition
Dim oneCell As Range

For Each oneCondition In Range("A:A").FormatConditions
    For Each oneCell In oneCondition.AppliesTo.Cells
        With oneCell
            If .Interior.Color <> .DisplayFormat.Interior.Color Then
                MsgBox oneCell.Address " CF active"
            End If
        End With
    Next oneCell
Next oneCondition

 

[qnguyen]

Thank you. I ended up modifying mikererickson's solution, though his works fine. Just need to change

MsgBox oneCell.Address [COLOR=#ff0000]&[/COLOR] " CF active"

I used

Dim cellcfexist as Range

If Range("A:A").FormatConditions.Count > 0 Then
        For Each cellcfexist In Range("A:A")
            If cellcfexist.DisplayFormat.Interior.Color <> 16777215 Then 'this color is excel's standard white'
                MsgBox cellcfexist.Row
            End If
        Next cellcfexist
    End If