IF BLANK turn COLOR for Range

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
601
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(
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top