IF BLANK turn COLOR for Range

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
This should be simple:

Highlighting a range of data
Want to define within the Conditional Format to turn all highlighted cells within that row ( range ) gray if any of the cells in column E:E are empty.

IF(ISBLANK ?
INDEX(
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
yes, the function you provide works well for singular cells - but I need to highlight a "range"...
Example:
I've highlighted A2:L500
and need it to turn all cells to grey that have a blank in column E

=ISBLANK(E:E) turn to grey - within the conditional format doesnt work
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Do you need each row triggered by the occurrence of a blank in the E cell for that row? If so, try the following condional format:

Use Formula Is and set it to =ISBLANK($E2) for cell A2, set the formatting as desired. Then copy the formatting to A2:L500. This should grey out each cell from A to L when E is blank on a particular row.
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

You need to copy the conditional format across all the cells. The easiest way to do this is to use the format painter. Be careful to lock/unlock the cells for coping down.
 

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
As I was awaiting a response - I performed the test on a brand new sheet and got good results.
I believe the sheet that was sent to me had other format limitations upon it - that was keeping it from colorizing properly.

* I created the new sheet within the same file,
* Left row 1 for headers....
* Highlighted the blank row 2 ..... A2:Q2
* Conditionally format: =ISBLANK($E2)
* Copied the row down through row 600 ( which made the whole page "grey" )
* Went to the original sheet full of data, highlighted all
* Went to new sheet, EDIT > Paste Special > VALUES
and everything lit up beautifully with the appropriate rows GREY and all others left white....

THANKS for the help though! -- this ISBLANK for the range works perfectly on a clean sheet.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
ChrisOK said:
This should be simple:

Highlighting a range of data
Want to define within the Conditional Format to turn all highlighted cells within that row ( range ) gray if any of the cells in column E:E are empty.

IF(ISBLANK ?
INDEX(

Sorry, I didnt read the post properly. Are you saying for instance if cell E1 is blank then format all of row 1 in your selection or if E1 is not blank then do not format the row?

=ISBLANK($E1)

This means every cell in the row will within your selection will look at the corresponding value of E & row and if blank will apply formatting. Because the row is relative (hasnt got the absolute $ sign) then this should work.

hth
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,390
Members
414,234
Latest member
grlevesq

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