Change cell colour with IF formula

[(Neil88)]

I want to change a blank cell next to a value in another cell to show a colour.

I am thinking =IF(E5>90,"green","red")

The formula would be written in E6 next to the value in E5.

Can i do this to show the colour green or red next to the value to say if it is higher or lower than 90?

Thanks for comments.

[José Augusto]

Try using Conditional Formatting

Select E6
Goto Conditional Format-->new rule-->Using a formula
=E5>90
format as green
new rule
=AND(E5<=90,E5<>"")
format as red

[etaf]

use conditional formatting to change the colour

you are looking at the cell row beneath

so is this JUST E6
or are other cells involved

select E6
goto conditional formatting
enter two rules
using

=E5>90
format Green

next rule
=E5<=90
format RED

[Aardigspook]

If you want the word green or red, then your formula is fine.
(Though consider what you want if E5 is equal to 90 - if you want green for that, use >= instead of >.)

If you want the other cell to be filled green or red, use Conditional Formatting. Select the cell, click 'Conditional Formatting' on the 'Home' tab, select 'New Rule' then 'Use a formula to determine which cells to format'. Enter this in the formula box at the bottom of the dialogue box:

Please Login or Register  to view this content.

Then click the 'Format' box and select green fill.
Now just fill the cell red using the normal cell colour fill button - it'll then be red whenever E5 isn't >90. (Alternatively, repeat the above with the <90 (or <=90) formula and red fill.)

[(Neil88)]

There are more than one cells with values. E5 but under each one I want a color, red or green.

When I get to Conditional Formating I get a choice of five "Styles" (2 colour scale, 3 colour scale, date bar, icon sets, classic)

Which one do i choose here to put in any formula?

The biggest problem is that I dont want to colour the value I want the cell next to it to show the green or red.

[(Neil88)]

There are more than one cells with values. E5 but under each one I want a color, red or green.

When I get to Conditional Formating I get a choice of five "Styles" (2 colour scale, 3 colour scale, date bar, icon sets, classic)

Which one do i choose here to put in any formula?

The biggest problem is that I dont want to colour the value I want the cell next to it to show the green or red.

[etaf]

are the rows you want to colour all EVEN

we are using a formula
should see new rule at the bottom of the menu

[José Augusto]

See the file

[(Neil88)]

Hi Jose Augusto,

This is what I need, the colour just below if horizontal (or opposite vertical), but how did you do this?

Thanks for you help Jose.

Neil88

[(Neil88)]

No they are not even, for example the values would be next to each other in a row (or below each other vertically)
(E5) (F5) (G5)
100 89 78
Green Red Red

Usually it just lets you colour the cell witht he number in it but i want it just below in a row (or to the right vetically).

[etaf]

but i want it just below in a row (or to the right vetically).

how do you decide which cell ? any rules , that could be applied to excel ?

this is sounding more like a VBA solution ,

you have E5, F5 and G5 with a number in
will all the colours be in row ^ or
H5 for G5 ?

[José Augusto]

Hi

When you create or manage a rule in a CF you can apply that to a range e.g. E6:M6. You can also copy the format in E6 and paste them to E6:M6.

[(Neil88)]

so if I had a number in E5, 89, in E6 it would be red because it is lower than 90. Then a number in F5, 99, F6 would be green below.

If it were vertical, E5, 89, it would be red in F5. Then E6, 99, would be green in F6.

[etaf]

and what decides.
if E5 is 89
do you colour E6 or F5 ?
how do you decide which cell to colour ?

[(Neil88)]

89 is just a number which could relate to anything, this is in E5 and I want to know if it is greater or less than 90 and the result would be below it in E6. The result of whether it was greater or less than 90 would show up in E6 as red. Then (in a row) the next number in F5 which is 95, this is greater than 90 so F6 would be green.

If i were doing this vertically E5 number (89) and F5 colour red and E6 number, F6 colour.

[etaf]

If i were doing this vertically

so these are separate spreadsheets you would do this to
not the same one

we have replied with the rules for colouring the cells

and José Augusto has posted an example and how to setup

did you follow and manage to reproduce that rule on your spreadsheet ?

[(Neil88)]

Ok i have got it now. Thanks everyone for your help.

I went down to the 'classic style" to put in the formulas there.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.