x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 668

# Excel - Change cell color based on value of another cell

I am running Office 2000 on an HP z210 running Windows 7 Pro.

I have an Excel spreadsheet.  In it is a cell containing a date.  The cell is A1
I have another cell which tracks price changes.  The cell is D1

In A1 I have conditional formatting .  The formula is =D\$1\$<10.00.  The cell turns red when this condition is true.

My question:
I'd like to have cell A1 turn orange if the price is < 5.00   .  But my guess is the logic of the conditional format would get confused.  A price which is < 5 is also < 10.

I am not an Excel expert.  What would you do to get cell A1 to turn orange when the price is < 5 and turn red if the cell is < 10 but greater than 5?  Please give me  a detailed answer.

0
donpick
1 Solution

Commented:
With Excel 2003 and before, Conditional Formatting stops with the first TRUE criteria. So the order of your two criteria is important. I suggest putting the orange criteria first, then the red:
=AND(D\$1<>"",D\$1<5)             orange criteria
=AND(D\$1<>"",D\$1<10)           red criteria

I suggested an AND function with a test for cell D1 being blank. If this is not possible, then you could use:
=D\$1<5             orange criteria
=D\$1<10           red criteria

If you intend to copy the Conditional formatting down to other cells in column A, then I suggest deleting the \$:
=D1<5             orange criteria
=D1<10           red criteria
0

Author Commented:
Thank you for your help.  I do not work with Excel often .  Very interesting solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.