0

I want to give a message box "Please resolve errors" if any cell in the range ("H1:H100") has fill color RGB = (255,179,181).

But the trick is that the cell in this range has this RGB on some conditional formatting condition. By default it has a different RGB.

Tried using

for i=1 to 100
    if Cells(i, 8).Interior.Color = RGB(255, 179, 181) then
        msgbox "resolve errors"
    end if
next i

Even if the cell color is RGB(255,179,181) it is not going inside the loop because this RGB is on conditional formatting.

Community
  • 1
  • 1
Surbhi Manocha
  • 155
  • 1
  • 4
  • 15
  • Possible duplicate of [How to get the background color from a Conditional Formatting in Excel using VBA](https://stackoverflow.com/questions/45122782/how-to-get-the-background-color-from-a-conditional-formatting-in-excel-using-vba) – Egan Wolf Nov 27 '17 at 06:57
  • Conditional formatting overrides cell fill that has been set manually. Conditional formatting is applied with rules. You can use VBA to test if the rules that trigger the CF apply to that cell by testing the cell for the same conditions as the CF. That can be done with VBA and you will know if any, and if so, which of the CF applies to the cell. If no CF applies, then just check the fill color with the regular VBA statements. – teylyn Nov 27 '17 at 07:01

1 Answers1

0

.DisplayFormat.Color will return the "regular" fill color

.DisplayFormat.Interior.Color will return the color that conditional formatting has set the cell.

so:

for i=1 to 100
    if Cells(i, 8).DisplayFormat.Interior.Color = RGB(255, 179, 181) then
        msgbox "resolve errors"
    end if
next i

but it's kind of a weird/terrible way to check if the user has errors. Why not check the values of the cells, the same way conditional formatting does?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • I can imagine that performance can be a reason. If conditions are very complex and worksheet already did that job, there's no point in doing it again and checking background doesn't sound like something very hard to do. – Egan Wolf Nov 27 '17 at 09:08
  • @EganWolf - "Very complex" conditions would call for some irregular methods for sure, and I am the first to do "whatever it takes that makes it work" (you should see some of my stuff!)... but I'm not convinced that's the case here. ...I think it may have been you where I first heard about the [XY](https://meta.stackexchange.com/a/66378/370758) problem :) – ashleedawg Nov 27 '17 at 09:14