Conditional Formatting

JSECFHFNREELYL

New Member
Joined
Oct 24, 2011
Messages
2
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.

Please Help!!

Thanks,
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

asking

Board Regular
Joined
Sep 5, 2007
Messages
226
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.
 
Last edited:

JSECFHFNREELYL

New Member
Joined
Oct 24, 2011
Messages
2
Hi -

Thank you for your response. If I may ask a follow up question.

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
 

asking

Board Regular
Joined
Sep 5, 2007
Messages
226
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top