Conditional Format - Highlight only blank cells in row if empty (Not entire row)

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
579
Office Version
  1. 2016
In conditional format, if I want to highlight any cell in a row that is blank , IF Column A states "Rejected" (Cells go from A: BG ) how do i do this please?
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
For row 1, the formula would look like:
Code:
=AND($A1="Rejected",A1="")
and choose your highlighting color.

To apply this to a whole large range, select all the cells that you want to apply it to, and then write the formula as it applies to the VERY first cell in your range.
If you use the Absolute/Relative range references correctly (like I did above), it will automatically adjust for all other cells.
 
Upvote 0
In conditional format, if I want to highlight any cell in a row that is blank , IF Column A states "Rejected" (Cells go from A: BG ) how do i do this please?

if the blank is a result of a formula then
=AND(B1="",$A1="Rejected")

If the cell is just empty
=AND(ISBLANK(B1),$A1="Rejected")

either or
=OR(AND(ISBLANK(B1),$A1="Rejected"),AND(B1="",$A1="Rejected"))


highlight the cells starting with B1 and apply the formula that is relevant to you as a conditional formatting formula
 
Upvote 0
I should have clarified, that my solution assumes that you are including column A in your range selection (kind of "assumed" when I said row 1, but may not be 100% clear).
Obviously, column A will never be highlighted, as it is impossible for both conditions to be true for any value in column A.
So you could, alternatively, start your range selection in range B, like Twollaston did.

They should both work just fine, though looking back on it, applying it to one less column is probably a little more efficient.
 
Last edited:
Upvote 0
thank you - works perfectly
For row 1, the formula would look like:
Code:
=AND($A1="Rejected",A1="")
and choose your highlighting color.

To apply this to a whole large range, select all the cells that you want to apply it to, and then write the formula as it applies to the VERY first cell in your range.
If you use the Absolute/Relative range references correctly (like I did above), it will automatically adjust for all other cells.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

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