Using icon sets in conditional formatting

**howardphillis** · 11-16-2009, 06:14 AM

Hello

I am trying to set up conditional formating to use icon sets (3 arrows) to demonstrate monthly trends. Each column in my header row is a month and then I have 4 rows below containing figures. I just need to display a green up arrow if the value in B3 is higher than B2, yellow if it is the same and red if it is lower. I then need to expand this across all columns so that B4 compares to B3, B5 to B4, etc. I do not need to compare across rows (ie B3 to C3)
This sounds like it should be easy to do but I am struggling to get it to work. Any help greatly appreciated.
Howard

**JeanRage** · 11-16-2009, 06:29 AM

Hello,

Among many possibilities ... see attached ...

HTH

**teylyn** · 11-16-2009, 06:32 AM

Hi,

you'll need to use a formula to determine the character for the cell. You can choose arrows from the wingdings or webdings font sets that point in the right directions.

These arrows will have an equivalent character in a font like Arial. Use conditional formatting and check for the existence of that character in the cell.

For example, the character "ñ" (=Char(241) formatted in wingdings is an up arrow

If a formula determines that the cell shows a "ñ", that cell can be formatted with wingdings font to show the up arrow.

Create a conditional format - Highlight cell rules - Equal to and enter

ñ

in the first box, then select green for the font color.

Apply similar rules for the other characters,

=char(240) for arrow right
=char(242) for down arrow.

hth

**teylyn** · 11-16-2009, 06:34 AM

Much nicer, your arrows, Jean!

**howardphillis** · 11-16-2009, 08:09 AM

Jean

sorry but my firewall is blocking that file. Any chance you could zip it and repost?

Many thanks

Howard

**teylyn** · 11-16-2009, 08:13 AM

Hi, I'm just quoting from the file you can't download:

[code]Use following three letters. If formatted in Wingdings you get
é {up arrow}
è {right arrow}
ê {down arrow}

here are the char() codes

é 233

è 232

ê 234

The arrows look much nicer than the ones I suggested, and the coloring with conditional format stays the same approach.

hth

**howardphillis** · 11-16-2009, 08:53 AM

OK, so are you saying there is no way to use a formula with conditional formatting to achieve this? I have to manually paste the arrows into each cell?

**JeanRage** · 11-16-2009, 09:05 AM

Originally Posted by howardphillis

OK, so are you saying there is no way to use a formula with conditional formatting to achieve this? I have to manually paste the arrows into each cell?

No ...
The solution is to use the specific characters in the formula you are using when setting up your conditional formatting ...

Should you have a difficulty, just post a sample worksheet ...

HTH

**Andy Pope** · 11-16-2009, 09:07 AM

If you want to use the 2007 icons CF you will need to use a help column.
This column will use a formula to perform a comparison test resulting in 2 or 1 or 0.

The CF can then be set to use Number
>=2 Green up arrow
>=1 Amber arrow
else Red down arrow

The numbers can be suppressed in the helper column.

So for values in B2:B5 use this formula in C3 copied down to C5

=IF(B3>B2,2,IF(B3=B2,1,0))

**howardphillis** · 11-16-2009, 09:16 AM

I've just realised that I was a bit misleading in my original post. I didn't mean that I was comparing B3 to B2, I meant C2 to B2, and D2 to C2, etc.
But I think I get the general principle Andy, thanks.

Using icon sets in conditional formatting

LinkBack

Thread Tools

Rate This Thread

Display

Using icon sets in conditional formatting

Re: Using icon sets in conditional formatting

Re: Using icon sets in conditional formatting

Re: Using icon sets in conditional formatting

Re: Using icon sets in conditional formatting

Re: Using icon sets in conditional formatting

Re: Using icon sets in conditional formatting

Re: Using icon sets in conditional formatting

Re: Using icon sets in conditional formatting

Re: Using icon sets in conditional formatting

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions