Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Nov 1 '10 at 12:36

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 1 down vote accepted

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.

Value 1

Value 2

Value 3

share|improve this answer
    
That's a great idea, using multiple text boxes with static formatting and "conditional text" to get around this limitation. But with all the steps about making the "888" appear like a box, it was a little hard to understand the core idea. I tried out the overlaid texboxes and multiple cells, and it works just as well with a Wingdings "n" (a filled-in box) at 200pt. Thanks! Hope this helps the next Googler! –  kcrumley Apr 9 at 20:58
    
I hope there's some kind of badge for "Question asked on StackOverflow during the beta, before SuperUser.com even existed, and migrated to another site, and accepted 6 1/2 years later". –  kcrumley Apr 9 at 21:00
1  
@kcrumley All the extra outlines and stuff were just to make it that much larger in case you need shapes so large that you hit the font size limitation. That would be some big boxes, though... –  Engineer Toast Apr 9 at 23:01

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.