Can graphics be used with conditional formatting?

C

Chris C

I'm trying to figure out if graphical indicators can be used with conditional
formatting. I want to do more than just color the font or fill the cell.
 
G

Greg Wilson

Check out the different font types such as Wingdings, Marlett, Webdings.
These are symbols instead of alphabetic characters. You can conditionally
change the font colour from being the same as the interior colour (symbol
therefore hidden) to the desired colour (symbol visible).

Regards,
Greg
 
C

Chris C

Thanks for the tip Greg, but I guess my Excel knowledge is not completely up
to par. I thought that for conditional formatting to work, there has to be a
value in that specific cell that meets the criteria to be conditionally
formatted. If I insert a symbol (non-text, non-numeric character), how do I
set the conditional formatting for a non-alphanumeric character in that cell.
Do I need to reference another cell or do some type of lookup? (i.e. the
result of a formula appears in B10, if that result is greater than or less
than the value in A10, format the cell in C10 to show a Wingding with
different font and background color).
 
G

Greg Wilson

BTW, the characters ARE alphanumeric. It's just that when they are formatted
as one of these font types they appear to be symbols. For example, the letter
"a" in Marlett appears to be a checkmark. The letter "n" in Marlett appears
to be a big dot. There is a technique for making your own checkboxes using
Marlett and the letter "a" that, unlike using real checkboxes, requires no
maintenance if the amount of data changes.

Example of using conditional formatting in conjunction with Marlett font to
flag dates that have expired:

a) Select cells C1:C10.
b) Select Format > Cells > Font tab.
c) Select Marlett from the list of font names.
d) Select the Color dropdown and select white (same as cell interior).
e) Close the dialog.
f) Select Format > Conditional Formatting (with C1:C10 still selected).
g) Select "Formula Is" in the dropdown.
h) Enter the following formula in the formula window:
=AND(B1>0, B1<TODAY())
i) Click the Format button (the Format Cells dialog will appear).
j) Select the Font tab.
k) Select the Color dropdown and select red from the color palette.
l) Click the OK button to close the dialog.
m) Click OK again in the Conditional Formatting dialog to close.
n) Enter the letter "n" in all cells in C1:C10.

Now enter dates in the range B1:B10. If these dates are less than today then
a red dot will appear in the adjacent cell in column C indicating expiry.
Note that the above formula is relative and will always reference the
adjacent cell in column B.

Example of how to view the different symbol options:

a) Enter the following formula in cell A1:
=CHAR(ROW())
b) Drag the formula down to cell A255.
c) Now format this range with different font types, particularly:
Marlett, Wingdings (3 series), Webdings, Map Symbols.

Regards,
Greg
 
Top