?
Solved

Excel - Change cell color based on value of another cell

Posted on 2013-06-29
2
Medium Priority
?
662 Views
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.
0
Comment
Question by:donpick
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 2000 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
0
 

Author Closing Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

621 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