Excel/Auto-Coloring a Cell Contingent on the Adjacent Cell's Number Value
I'm using Office Standard 2010 Excel.
I'm looking to alter a cell with data's color, depending on the numerical value entered in the adjacent cell.
i.e. 1= purple 2= red, etc.
I'm aware of how to alter the conditional format to make a cell, say c15, turn purple by entering
a certain number. However, I'm not sure how to make it turn the cell the next column over that color,
since the cell I desire to change the color for will already have data contained that's not numerical.
Hypothetically, let's say C15 has data (holidays) in it. I want to turn it purple without manually doing
it. Would a nearby cell, b15, need to have a formula to drive this coloring for c15, or rather
what's the most efficient way of doing this?
Thanks in advance for any of your help!
The cell that will turn color must have conditional formatting applied. The condition does not need to refer to the cell that will change color. It can be dependent on another cell. In you example, you would apply conditional formatting to C15. Say you select C15 and then click conditional formatting from the home tab.
Choose: New Rule
Choose: Use a formula to determine which cells to format
Under "Format values where this formula is true" will be a textbox. Put a formula like
whatever your condition is (the formula can be quite complex it you wish - it must just return true or false or the equivalent).
then click the format button and choose the formatting you want.
Note you could have selected multiple cells. But in any selection of multiple cells, one cell is the Activecell (highlighted differently). You would enter your formula relative to the ActiveCell. You would use absolute and relative cell references to create the exact formula you need.
If that isn't clear, send me a sample workbook to email@example.com and tell me exactly what you want to do.
---------- FOLLOW-UP ----------
QUESTION: Thanks so much, Tom!
What you said makes perfect sense, and I succeeded in doing this
on an individual cell basis.
My next question would be how to make these rules apply to an entire column, say C1-C25 without manually doing so? I tried selecting a range, i.e. $C$1:$C$25, to apply the rules to, and it didn't work.
Any insight? Thanks!
>Note you could have selected multiple cells. But in any selection of multiple cells, one >cell is the Activecell (highlighted differently). You would enter your formula relative to >the ActiveCell. You would use absolute and relative cell references to create the exact >formula you need.
So you would select C15:C25 with C15 as the activecell in the selection.
then apply your conditional formatting as if you were doing it to only cell C15
the formula would be
as an example. I make the B/column reference absolute (precede with a $ sign) and leave the row reference (15) as relative. So when the format is applied to C16 the formula will appear as
Hope that gives you the information you need.
if you need an example, send a sample workbook to firstname.lastname@example.org and tell me what you want to do (what the conditions are).