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

No, but you can fake it

You can fake it but it takes some setup. It will work unless you need really big shapes. Here are the steps for creating n different conditional format shapes in Excel 2010.

---

tl;dr Use 1 extra cell and 1 text box for every condition you want. Each text box is setup to show giant text with huge outlines and shadows, all the same color. The extra cells are setup to only show text if the value meets the condition.

---

1. Create n helper columns that will show blank unless the value meets the correct condition. A simple example for n = 3 cases might be: (we'll get into why I used "888" later

   =IF(A1=1,"888","") =IF(A1=2,"888","") =IF(A1=3,"888","")
   For now, though, just fill the helper columns with text, not a formula.

2. Format the text color in each helper column to be correct for that condition

3. Create 1 text box that is the correct size and put in any text (E.G. "Hello World")
4. Format the borders however you like but make sure the fill is No Fill
5. Right-click on the text and click on `Format Text Effects..." near the bottom of the context menu
6. Set the Text Outline to a solid line in the correct color for condition 1
7. Set the Outline Style to 25pt or something else very high
8. Set the Shadow to Outer > Offset Center with a transparency of 0% and a size of 200%
9. Copy this text box n-1 times so now you have n total
10. Change the color settings for each in turn to be whatever you need
11. Go back and set the formula for each to be one of the helper columns, being sure to connect it to the correct one
12. If the text fill in the text boxes is not correct, go back into each one and change it
13. For each text box, set the font size to 70 or whatever size is needed to fill the box entirely with color
14. Once everything is setup, go back and change the helper columns to be formulaic. Have them show text that takes up lots of space like 8, #, or some box symbol.
15. Test it to make sure that, for each value, one text box shows color and the rest show no fill
16. Stack up all the text boxes (Align > Left and Align > Top might be helpful) and group them so they won't get messed up as easily

---

Here are some screenshots of a mockup I did.
Note that which box has color changes as the value changes. If I had stacked these up, it would look like a single shape changing color.

Answer 2

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.