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

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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

ADVERTISEMENT

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

Watch MrExcel Video

Forum statistics

Threads
1,133,272
Messages
5,657,769
Members
418,412
Latest member
fehr56

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top