Conditional Formatting on pictures

Hi Lacey,

While it is not difficult with a few lines of VBA code to change the color fill of a circle (drawing object or Shape object), there is an easier way that permits the use of Conditional Formatting. Simply get a filled circle from a font such as Monotype Sorts, or use a filled circle character from the Arial Unicode character set using the Charmap.exe utility. Then you can change the color of the circle using standard font color control with Conditional Formatting.

To use the Monotype Sorts approach, simply change the font of the cell to Monotype Sorts, then enter the character "l" (lower case L). It should appear as a small circle. Now just increase the font size until the circle is the size you want.

If you want to use the unicode approach without changing from the Arial font, simply paste the character "●" into the cell, copying it directly from the text of this posting.

Keep Excelling.

Damon

Conditional Formatting on Pictures

Thank you so much! That is a much easier alternative to what I was thinking I would have to do. But now I run into another issue that I didn't think of before...is there a way to have my data in one cell and my "stoplight" in another (that way I can hide the data columns and we can just look at the colors to see what's happening)? When I have the "●" in one cell I can't put number values in with it or when the conditional formatting is referencing the data against the other cell, it doesn't see it as a number value.

I guess what I want is excel to look at cell A and determine if the data is greater than the data in cell B. If it is greater, I want the "●" in cell C to turn green. Is that possible to do? I wouldn't know where to begin...

Thanks!

Lacey

Hi Lacey,

Yes, the Conditional Formatting formula can reference other cells. When you do the Conditional Formatting on the cell in C, select as the condition type "Formula Is", then enter as the condition

=(A1>B1)

which yields TRUE if A1 > B1, and of course set the font color to green.

By using two conditions you could easily have the circle turn green if A1 > B1, amber if A1=B1, and red if A1 < B1. The third (missing) condition would not be needed since you can simply set the font manually to the default color (red?) and let the conditional formatting override this when their conditions are met.

Keep Excelling.

Damon

That did the trick! Thanks so much for your help!