Conditional Formating with Icon set - How to change the color

[Jocelyn]

Hi there,
I'm trying to change the color code for the Icon set(arrows).
The report I run and analysis, it show increase value as bad, and decrease
as good (improvement).

However, the arrows icon default as increase - green color; decrease - red
color.

Is anyone know how can I change the color code ? I need to show increase as
RED color and decrease arrow as Green. The opposite as the default color.

Thanks.

 

[OssieMac]

Hi Jocelyn,

I assume you are working with xl2007.
Select the Conditionally formatted cells.
Click Conditional format button.
Select Manage rules.
Select the particular rule.
Click Edit Button.
Near the bottom center of the dialog box is a check box to Reverse Icon order.

 

[Jocelyn]

Hi OssieMac,
Good day & thanks for the prompt reply. I tried before, however it doesn't
work as what expected. It just reversed the sequence but not the color. The
color still stay as Increase arrow = Green; Decrease arrow = Red

Any idea what else should I do ?
Thanks.

Jocelyn

 

[OssieMac]

Hi again Jocelyn,

I see what you mean now and yes, your options are somewhat linited.

No doubt you have thought of changing the type of icon to traffic lights or
something that is not specifically arrows and simply go for green is good and
red is bad although I can appreciate the benefit of having the arrows up and
down.

Another way is to insert a column beside the values and use an If statement
to insert symbols from the Windings and then apply conditional formatting to
the arrows.

For this explanation assume that the data is in column A starting at A1
The arrows will be in column B starting brom B1
The data in column A is random values from 1 to 10 and cells < 5 will have
down arrows and green, 5 will have horizontal arrows and orange, and greater
than 5 will have upward arrows and red.

Set the font for column B to Wingdings.
Insert the following formula in B1. (Note that you will only be able to read
the formula in the formula bar. You won't be able to read it in the cell with
wingdings font)
=IF(A1=5,CHAR(232),IF(A1>5,CHAR(233),CHAR(234)))

Copy the formula down column B.

Select all cells in column B from cell B1 to last cell to be formatted.
(Note that I find this the easiest way and then set the formula for
conditional formatting as if setting it for the first cell only and let Excel
apply the formula to the other selected cells)
Select conditional format.
Select New Rule.
Select 'Use a formula to determine which cells to format.'
Insert the following formula.
=B1=CHAR(232)
Set the font format to orange and then click OK/Apply etc to finish.
Select Conditional format -> Manage rules.
Select New rule.
Repeat the above but this time the formula is =B1=CHAR(233) and the format
color is Red.
Select New again and use formula =B1=CHAR(234) and format color is green.

To find the correct code for particular symbols, Select Insert ribbon tab
and then Symbols. Select the Windings font and then the required character
and the code is displayed at the bottom of the dialog box. Note that when
using the code to insert the character in another cell, the cell must have
the font set to Wingdings. When inserting the character from the Insert
symbol, the font is automatically set.

Hope that this helps and feel free to get back to me if you need any
clarification. However, it is bed time for me in my part of the world so it
will be a while before I will answer again.