Change Color of cell based on input in adjacent cells?

[RyansPlace]

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

 

[RyansPlace]

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]

Hi and welcome to the Board!!
For "PickUp", select C5:C599 and open CF dialog.
Choose Formula is

=$B5=""

'Format Red
For Destination, select E5:E999
Formula Is

=($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]

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

 

[RyansPlace]

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]

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]

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]

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