Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Excel - Change cell color based on value of another cell

Posted on 2013-06-29
Medium Priority
654 Views
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
Question by:donpick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 81

Accepted Solution

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

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

Featured Post

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Suggested Courses
Course of the Month7 days, 14 hours left to enroll