Excel - Change cell color based on value of another cell

Posted on 2013-06-29
Last Modified: 2013-07-02
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.

Thank you for your help.
Question by:donpick
LVL 81

Accepted Solution

byundt earned 500 total points
ID: 39287337
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

Author Closing Comment

ID: 39294824
Thank you for your help.  I do not work with Excel often .  Very interesting solution.

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question