Formula to change colour AND text in cell depending on value in adjacent cell

Hi there,

I'm creating a spreadsheet for a company that owns nearly 40 vehicles. This spreadsheet is for them to keep track of their vehicle's tyre treads.
I would like for when I enter in B10 a value between 0-2 the adjacent cell (C10) would turn red and the text to change to "URG".
I know how to change the colour in conditional formatting but am struggling to find a way to also make the text change.

I'd also like to apply the following rules:
- When I enter in B10 a value between 2-4, the adjacent cell (C10) would turn orange and the text to change to "1 MTH".
- When I enter in B10 a value between 4-6, the adjacent cell (C10) would turn green and the text to change to "OK".
- When I enter in B10 a value greater than 6 the adjacent cell (C10) would turn white and the text to change to "NEW".

Thanks so much for your help!

H

Thanks Ali, good question...
Let's say:
If B10 = 2 it's orange
If B10 = 4 it's green
If B10 = 6 it's white

Ali, this is awesome, thanks so much!

I'm now struggling a little bit with the colours... The formula I used for the "URG" rule was =$B$10<2, which obviously worked brilliantly in conditional formatting to change the colour to red. However, I've tried creating the same rules for the orange, green and white colours but have obviously stumbled across the need to put in a greater than symbol. For example, if the tyre tread is greater than 2 but less than 4, I need it to turn orange but not white which is currently overriding all other formulas.

Also, is there a way I can copy and paste all formulas applying to 2 cells and apply the same rules? I could do this manually, but it would take an awfully long time seeing as I have 40 vehicles and 4 tyres on each vehicle!

Thanks again!

Set the "Applies to:" in CF to the required range(s)