Solved

Excel - Change cell color based on value of another cell

Posted on 2013-06-29
2
616 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 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
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

680 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