Trying to have conditional formatting based off of the value in a different cell

I have about 60 rows of 6 columns with numbers in most of them.

I want to highlight each cell of column E, based on the numbers that are in columns C and D (D will always be higher than C). If Column E is higher than column C but lower than D it should be one color. But If E is higher then D it should be another color, and if it is lower then C it should be a third color

I am assuming it can be done with conditional formatting, but I have never done conditional formating without it being a fixed number.

Thank you

Any chance you can attach a sample Excel workbook? The yellow banner at the top of the screen tells you how to.

Pete

Here is an attached example.
So in this example, I would want:
E2 to be Green (because it is higher than D2)
E3 to be Red (because it is higher than C3 but lower than D3)
E4 to be Green
E5 to be Yellow (because it is lower than c5)
E6 to be Red
E7 to be Yellow

Column D will Always be higher than column C

Thank you

Select all the cells from E2 down (to E7 in your example file), then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box which pops up:

=AND(E2>C2,E2>D2)

Click on the Format button | Fill tab and choose green, then OK your way out. With the cells still highlighted you can repeat, but use this formula:

=AND(E2>C2,E2<=D2)

and choose red for this condition. Repeat once more and use this formula:

=E2<=C2

and set this one to yellow. Note that you don't need the AND for this third formula if you can guarantee that D2 is always larger than (or equal to) C2.

When you close the dialogue box you will find that Excel will automatically adjust the cell references to suit all the cells that were selected, so you can do them all in this one operation.

Hope this helps.

Pete

I've just noticed that you have a location in what looks like Spanish, so you may need to use semicolons ( ; ) instead of commas ( , ) in the formulae, depending on your regional settings.

Hope this helps.

Pete

Thank you, I tried it but see attached the colors aren't reflecting properly.
I am getting
E2 is Red instead of Green
E3 is Green instead of Red
E4 is Yellow instead of Green
E5 is Red instead of Yellow
E6 is Yellow instead of Red
E7 is Yellow when it is supposed to be Yellow.

So its not even that the colors are mixed up. One time there is a yellow instead of a red and another time its a green instead of red.

Re the spanish, thank you for the heads up.

Thank you

I've set this up in the attached file. Does this do what you want?

By the way, you already had a condition set up for just E2, so I deleted that rule.

Pete

Yes thats perfect. I will test it on the full sheet and see how it reflects.

Thank you

That's good to hear.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete