You are here:

Excel/Auto-Coloring a Cell Contingent on the Adjacent Cell's Number Value

Advertisement


Question
QUESTION: Hello,

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!

ANSWER: India,

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

=$B15>10
or
=$B15="House"
or
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 twogilvy@msn.com  and tell me exactly what you want to do.  

--
Regards,
Tom Ogilvy




---------- 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!

Answer
India

>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

=$B15="House"
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

=$B16="House"

Hope that gives you the information you need.  

if you need an example, send a sample workbook to twogilvy@msn.com  and tell me what you want to do (what the conditions are).

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.