Change Color of cell based on input in adjacent cells?

RyansPlace

New Member
Joined
Dec 19, 2009
Messages
25
Hello,

I'm using Microsoft Excel 2003. I have a spreadsheet to log missions with several columns (column ID and title below):

B5:999 'PZ', C5:999 'Pickup', D5:999 'DZ', E5:999 'Destination'.

My intent is to have the 'Pickup' column highlight in red if there is no input in the 'PZ' column, but then revert to its original color once an entry is made in in the 'PZ' column.

Additionally, I'd like to have the 'Destination' column highlight in red when an entry is made in the 'PZ' column, but then revert to it's original color when an entry is made in the 'DZ' column.

Only input made in the 'PZ' and 'DZ' columns are X's indicating that portion of the mission was completed.

I'm familiar with the Conditional Formatting menu, but is only seems to look at a single cell. I'm trying to use multiple criteria to change the color of a cell based on input in adjacent cells. Can this be done?

v/r
-Ry
<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RyansPlace

New Member
Joined
Dec 19, 2009
Messages
25
Looks like a busy day. Bump for answer.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Hi and welcome to the Board!!
For "PickUp", select C5:C599 and open CF dialog.
Choose Formula is
Code:
=$B5=""
'Format Red
For Destination, select E5:E999
Formula Is
Code:
=($B5<>"")*($D5="")
NOTE: This will Color All cells in C until a value is enterd in B. I don't think that's what you want!! Do you only want the formatting to apply when something is also enterd in Column "A"?
MAybe a code appraoch would be better. Is that an Option?
lenze
 

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
You will have to use Formula instead of Value in conditional formatting. When putting in the conditional formatting criterion, change the drop down from "Cell Value Is" to "Formula Is". Then in C5, enter this criteria in Conditional Formatting -

=ISBLANK(B5)

Format it with red font. In E5, enter this criteria -

=AND(LEN(B5)>0,ISBLANK(D5))

Format this as red font as well. This should do.

Hope this helps !

Prabby
 
Last edited:

RyansPlace

New Member
Joined
Dec 19, 2009
Messages
25
To clarify. I only want C cells highlighted if a value is added to Column C, but not Column D. So basicly, an entry in column C is highlighted until I put an X in column D.

Column E cells should be highlighted if a value is added in Column B, but not column D.

I understand your instructions though. That might actually work for column C. Will it highlight a cell with no entry?

-Ry


<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">
 

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
To clarify. I only want C cells highlighted if a value is added to Column C, but not Column D.

-Ry


<INPUT id=gwProxy type=hidden><!--Session data--><INPUT id=jsProxy type=hidden *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}">
Did you want to say that C cells will be highlighted if there are values added in column B but not column D?
 

RyansPlace

New Member
Joined
Dec 19, 2009
Messages
25
Your right, I maint column C highlights with no input in B but reverts when an input is added.

prabby25101981 - your formula's worked. When I put an X in column B, the adjacent Column C reverts to the origional color as it is supposed to, but for some reason, the cells in column E two rows down highlight. Not the cells in the adjacent row as intended.

For example: I put an X in Cell B10 which causes C10 to revert to original, but the cells in E12 highlight instead of E10. Putting an X in D10 causes the E12 highlight to disappear. I can't figure it out.

-Ry
<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">
 

RyansPlace

New Member
Joined
Dec 19, 2009
Messages
25
Figured it out. Thanks for the help.

To change color using Conditional Formating of a cell with two other cell triggers:

Formula:
=AND(LEN(B410)>0,ISBLANK(D410))

B is a positive entry and D is a negative entry (nonspecific)

-Ry
<input id="gwProxy" type="hidden"><!--Session data--><input *******="if(typeof(jsCall)=='function'){jsCall();}else{setTimeout('jsCall()',500);}" id="jsProxy" type="hidden">
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,973
Messages
5,508,505
Members
408,688
Latest member
Bhojraj

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top