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.
Best Answer
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.
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.
Format the text color in each helper column to be correct for that condition
No Fill
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.