Conditional Formatting For Text Inside Shapes

[Louis.Shakespeare]

Morning,

I'm trying to complete a dashboard and have started to use shapes to make it look pretty. Inside the shapes I have them linked to cells however need some conditional formatting to go green or red depending if they are above or below average. I added the conditional formatting to the origin cell, but this didn't reflect in the shape.

I have attached an example I am working on, the shapes are all grouped at the moment, but i need to conditional formatting for each shape against if its below or above average (31.19%);

Excel Forum.xlsx

I have searched for other topics and didnt seem to find a solution.

Many Thanks

[6StringJazzer]

Conditional formatting can be applied only to cells, not text in shapes. If you need dynamic formatting of shape text then you need to use macros. I looked at your file and it would not be a huge effort but not trivial either.

[Louis.Shakespeare]

Thanks for the response, atleast I know where to direct my attention too.

So basically get the macro to check the value in the shape and highlight and change colour accordingly? Or check the actual origin cells?

I'm not great with VBA but always keen to learn so I will give it a go :-)

Thanks again.

[6StringJazzer]

Here is the basic idea. I am fairly experienced with VBA but not in working with manipulating shapes. This code does not work; I get an error on the With line. But this is the idea. You would have to have a case for each different cell that is linked to a shape.

I cannot figure out how to address the text in that box. The grouping may complicate matters. Also odd is that the big box at the bottom and the second box from the top are both named "Rounded Rectangle 24". I don't know how that's possible.

Please Login or Register  to view this content.

[Andy Pope]

No need for vba.

You just need to use multiple shapes to display the variation in formatting.
I have added 3 formula to columns M:O. One of the cells will display the value. You link that to 3 shapes and format each of the shapes to correspond to either red/green/black value.

I have done the first box and the value is now random so you can press F9 to see the value change

[Louis.Shakespeare]

Marvellous.

I won't get to have a look until I log back on at work but if I need any further help (which I'm sure I wont) I will use this thread to ask.

Thanks

[Louis.Shakespeare]

Morning,

The method of adding that additional formula doesnt work for when the text is green. I amended it to recognise the alternative argument =IF($I10>$I$15,TEXT($I10,"0.00%"),IF($I10<$I$15,TEXT($I10,"0.00%"),"") but turns all the text red for some reason and doesnt recognise when i want to highlight green.

I will try fiddle about on VBA in the mean time, will keep me busy.

Thanks

[Andy Pope]

Works for me. Guess you must be doing something different to my example.

[Louis.Shakespeare]

That's weird, so you can get it to work when the text should be green, using the same method i've used?

[Andy Pope]

Depends on which method you are referring to.

I the example I attached you can press F9 to recalc values. When green value appears in M9 then the textbox should be displaying green value.