Conditional Format cells based on values in range

[ARJUNDSOUZA]

Dear Excel Gurus,

I need your help, have checked past threads on CF however unable to find the answer to my question...

I am looking for CF formula wherein color codes are applied based on the value in cell.
Sheet attached for reference, wherein Col A contains the values and I want the formula I could use CF and result as in Col B and Col C

Thank you in advance for your support.

Kind regards
Arjun Dsouza

 

[ARJUNDSOUZA]

Sorry, the range should be as follows:

Score: 0 to +2.99 = Green
Score: +3 to +4.99 = Orange
Score higher than 5 = Red

 

[ETAF]

you have shown 3 colours , RED , AMBER , GREEN
so you need a RULE for each colour - Fill or FONT

so
what are the ranges of numbers to colour for RED
what are the ranges of numbers to colour for AMBER
what are the ranges of numbers to colour for GREEN

as i cannot make out from the example

 

[ETAF]

cross posted at same time
you have negative values in your example
3 rules to be setup Rules
=AND( cell<> "" , Cell >=0 , Cell <3 ) - colour Green
=AND( Cell >=3 , Cell <5 ) - colour orange
=Cell >=5 - colour red

select the range you want to colour - say
B2:B100
use the formula above
=AND( B2<> "" , B2 >=0 , B2 <3 )

 

[ETAF]

I have added a rule for column B with a fill colour
Red is =5 or higher
as you say Orange is to 4.99
which means 5 is not mentioned , so not sure what colour for 5 - so made RED

 

[ARJUNDSOUZA]

@ETAF Thank you for this, I tried further if the values were negative, could not work out the formulas

0 to -2.99 = GREEN
-3 to -4.99 = AMBER/ORANGE
-5 to -99.99 = RED

Appreciate your response.

 

[ETAF]

OK so

Score: -2.99 to +2.99 = Green
Score: +3 to +4.99 & OR -3 to -4.99 Orange
Score higher than 5 OR less then -5 Red

Do you never get anything below -99.99
Or any less then -5 you want red

=AND( cell<> "" , Cell >-3 , Cell <3 ) - colour Green
=OR( AND( Cell >-5 , Cell <=-3), AND( Cell >=3 , Cell <5 ) - colour orange
=OR( Cell >=5 Cell <= -5) - colour red

green
=AND( C2<> "", C2>-3, C2 <3 )
orange
=OR(AND(C2>-5,C2<=-3),AND(C2>=3,C2<5))
red
=OR(C2>=5,C2<=-5)

see column C - in attached
formulas are in E , F & G - just to show the working , not used in conditional formatting

 

[ARJUNDSOUZA]

Thank you for the sheet, works well to what I needed.

May I ask for some more assistance, this time - Cells A25:A31 are Days of the Week - I want to use CF wherein if cell value is Sat or Sun (Weekend) then Cell color + 3 cells on right should change to Grey. I hope this is not confusing, if this is possible. Sample file attached.

Thanks once again for all your assistance, already have learnt something new

 

[ETAF]

is a date or just txt
seems like text in your example
in which case

Then select the range
a25:C31
and use a formula
=OR($a25="sat",$a25="sun")
Note the $ , so it only checks column A , but applies the fill across to C