Conditional Formatting

Thanks:  0
Likes:  0

1. ## Conditional Formatting

Hi - This is tricky and hopefully you can help as have yet to find out how to do this.

I have a spreadsheet with 185 rows and 100 columns. Each cell has a formula which will produce either a 1,2,3,4,5,6,7 or 8. The color of the cells are based on the number with red being the lowest (1) and green being the highest (8) - using conditional formatting. What I would like to do is add arrows to the cell based on the numbers, in addition to the colors - also using conditional formatting (or any other suggesstion). The odd numbers deserve a DOWN arrow and the even numbers deserve an UP arrow.

So to boil it down, 1 is Red and it scales to green at #8. The arrows should be up for even numbers and down for odd numbers.

Thanks,

2. ## Re: Conditional Formatting

Hi there,

You can do it all with conditional formatting but you will need to set 8 rules, a rule for each number.

It depends on what type of arrows you require.

ALT 30 and ALT 31 are up - down arrows, if you want different arrows find the alt code
for those arrows.

I will show 1 rule for the first number 1 then continue in a like manner for the rest of
your numbers. Use ALT 30 for the Up arrow for even numbers

First select all the range of the numbers you wish to format > select conditional
formatting > select "Use a formula..." > type in =A1=1 (if that is the
first cell in your selected range) > click format > click fill > click red > click number >
click custom > in the type box highlight the general then while holding down the ALT key type 31 and 3 spaces
followed by the Hash symbol (you should now see an downwards triangle and a number in the sample box)
Click OK > Click apply.

Cheers.

3. ## Re: Conditional Formatting

Hi -

Unless I am mistaken this formula will change each cell based on the value in cell A1 (so if A1 is '1' then all cells with be red and have a down arrow). Each cell in my worksheet will produce a number between 1-8 and based on each cell I want excel to produce a color and arrow. So all cells with 1 will be red with down arrow; All cells with 2 will be red with up arrow; all cells with 3 will be lighter red with down arrow; etc...

Thank you for taking the time to help. I greatly appreciate it!

(I am using excel 2010)

Best,
J

4. ## Re: Conditional Formatting

Hi again,

No, A1=1 is a relative reference, it will only colour cells red in the selected range with a 1 in them ~ try it.
When it looks into the next active cell in the range selected the A1 will change to
=A2=1 and so on. If the formula was =A\$1=1 then it would colour all cells red in the
columns starting with a 1 If the formula is =\$A\$1 then if the first cell was a 1 then
it would colour all cells red, if it was not 1 in cell A1, no colour would be applied.

When you do your next rule for number 2, for your next colour/format the new formula =A1=2 still needs the same selected range as for the first rule.

Another thing I thought of was that you may want your arrows on the right of the number, if this is the case ~ change

in the type box, highlight the general then while holding down the ALT key type 31 and 3 spaces followed by the Hash symbol
(you should now see an downwards triangle and a number in the sample box) Click OK > Click apply.

To ~
in the type box, highlight the general then type the hash key (#) followed by 3 spaces, then while holding down the ALT key type 31
(you should now see a number then 3 spaces and a downward triangle in the sample box)
Click OK > Click apply.

Cheers

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•