conditional formatting

[lilrainbowsky]

Hello guys!! need your help!! :D
I want to do conditional formatting for the cells in my spreadsheet.
I have a coloumn of numbers, I want the numbers 1 to 100 to have a green upwards arrow, 0 to have the yellow arrow and -1 to -100 to have the red downwards arrow! i cant seem to do it right! please help!!!
THANKS GUYS!

[Ace_XL]

Like this..

[dogberry]

Hi

Is this what you are after


Chris

[lilrainbowsky]

Omg thanks so much ace_xl!!! :d

[benishiryo]

hi lilrainbowsky, welcome to the forum. do your numbers only include -100 to 100? if so, you can select the range you want your conditional formatting (for eg A1:A201), & go to Home -> Conditional Formatting -> Icon Sets -> More Rules ->
Green when value is >= 1
Type: Number

Yellow when value is >=0
Type: Number

Red when < 0

Icon Style: 3 Arrows (Coloured)

if you have a range outside -100 to 100, then you might have to do it outside the cell, say in Column B.
=IF(AND(A1>=1,A1<=100),CHAR(227),IF(A1=0,CHAR(226),CHAR(228)))
you can then do a conditional formatting on this range by going to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:

=B1=char(228)
font will be red for this

=B1=char(226)
yellow font

=B1=char(227)
green font

[lilrainbowsky]

thanks Chris!! it worked! :D

[dogberry]

Hi

Pleased it worked for you dont forget to mark your thread as solved and give the stars a click for those that helped.

Thanks

Chris

[vlady]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.