Excel - conditional formatting in Shapes?

Question

For a long time, I didn't think it was possible to have a Shape (e.g., Text Box) have a value set by formula, but it turns out you can. Similarly, it appears that you can't apply conditional formatting to Shapes (the option is grayed out in the Ribbon in Excel 2007); is there some secret way to work around this graying out, and apply conditional formatting to a Shape? In case the answer is different for different types of Shapes or formats, I'm specifically trying to conditionally apply different colors to a rectangle.

And the reason I want to do this at all is because we can't use macros for this specific case.

Answer 1

I'm not sure there is. In 2007, like older versions of Excel, FormatConditions can only be applied to Ranges, not Shapes.

However, if I'm understanding your situation correctly in that you can't use macros, then I'm not sure you'd be able to apply conditional formatting behind the scenes in any event ... and if you could use macros/VBA, then you wouldn't necessarily need conditional formatting; you should be able to run a procedure to format the shapes as needed. Something like the following should work, but I am guessing you've already discovered this part:

ActiveWorkbook.ActiveSheet.Shapes(1).Fill.ForeColor.RGB = RGB(0, 0, 255)

If you wanted to format the shapes based on values as they were entered in the worksheet, then yes, it would be easier to use conditional formatting if it were available.