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.

1 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.

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.