Conditional Formating with Icon set - How to change the color
 

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.

Answer: Conditional Formating with Icon set - How to change the color
 

Hi there! I can definitely help you with that. Here are the steps to change the color code for the Icon set:

1. Select the range of cells that you want to apply the Icon set to.
2. Click on the "Conditional Formatting" button in the "Home" tab of the ribbon.
3. Select "Icon Sets" from the drop-down menu.
4. Choose the Icon set that you want to use.
5. Click on the "Conditional Formatting Rules Manager" button at the bottom of the drop-down menu.
6. Select the rule that you want to modify.
7. Click on the "Edit Rule" button.
8. In the "Icon Style" section, click on the drop-down menu next to "Icon" and select the icon that you want to use.
9. Click on the drop-down menu next to "Type" and select "Number".
10. In the "Value" field, enter the number that represents the threshold for the icon set.
11. In the "Icon Style" section, click on the drop-down menu next to "Icon Only" and select "Icon with Text".
12. In the "Icon Style" section, click on the drop-down menu next to "Show Icon Only" and select "No".
13. In the "Icon Style" section, click on the drop-down menu next to "Reverse Icon Order" and select "Yes".
14. Click on the "OK" button to save the changes.

That's it! Your Icon set should now display the colors that you want.

Conditional Formating with Icon set - How to change the color
 

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.

--
Regards,

OssieMac


"Jocelyn" wrote:

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.

Conditional Formating with Icon set - How to change the color
 

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" wrote:

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.

--
Regards,

OssieMac


"Jocelyn" wrote:

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.

Conditional Formating with Icon set - How to change the color
 

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(A15,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.

--
Regards,

OssieMac

Hi OssieMac,
Your reply to Jocelyn gave me some idea on how to implement what i was trying to do, however, i am not quite there yet. This time, instead of arrows, i wanted to use 1/4, 1/2, 3/4 and full moon. I found the symbols under Arial Unicode MS, subset "Geometric Shapes". The character code were identified as follows :

1/4moon - 25D4
1/2moon - 25D1
3/4moon - 25D5
fullmoon - 25CF

However, these are in Unicode (hex) and when i applied your formula (just for a quick validation, like shown,

=IF(S4=5,CHAR(25D4),IF(S45,CHAR(25D1),CHAR(25D5)) ), i got an error.

So i converted the hex to ascii with 25D4 as 9684 and etc, it still wouldnt help. Please help!!! -- thanks -- geess