CODEWITHSUNDEEP
excelsearchconditional-statementsvba
Korean_Of_the_Mountain
Korean_Of_the_Mountain

Reputation: 1577

Search for cells with conditional formatting

I have a range of cells with conditional formatting where if the cell exceeds a certain threshold value, it will be filled with red (I believe it is .color = 255).

I'm trying to create a macro that will search for cells in that range that exceed the threshold by searching for cells with .color = 255. The macro isn't working for some reason. It is unable to see that the cells are red due to conditional formatting. It can detect cells that I simply change the fill as red manually, though.

Sub macro22()
   For Each m In Range("D7:L33")
      If m.Interior.Color = 255 Then
          ex = "exceedance"
      End If
Next

  Range("p22").Value = ex

End Sub

I know it is .color = 255 because if I record a macro to change the fill color of a cell to match the color I want from conditional formatting, that is the value it comes up with.

Upvotes: 0

Views: 1033

Answers (2)

Peter L.
Peter L.

Reputation: 7304

In case you have only one rule for CF you should check m.FormatConditions(0).Interior.Color value instead of m.Interior.Color - as more detailed response above) 0 represents the number of CF rules but starts from 0 (at least should be referenced so).

Upvotes: 0

barry houdini
barry houdini

Reputation: 46341

Color function doesn't return the colour if it's applied through conditional formatting. See here for alternative approaches

Upvotes: 0

Related Questions