Conditional Formatting with Self-Reference to ISBLANK

speth

New Member
Joined
Feb 18, 2013
Messages
27
I have a leading column A with some information in it, and columns B, C, D and G with data that must be filled in for support.

I want to have a conditional format to highlight those other cells, to remind the user that the information needs to be inputted and is currently missing.

Basically looking for like: Highlight IF NOT(ISBLANK(A1) AND ISBLANK(SELF)

I can do that one-at-a-time for the columns, and the condition is =AND(NOT(ISBLANK($A1)),ISBLANK($B1))

Is there a way I can mass-apply the condition to cover *Any* columns included in the Conditional Formatting selection? I don't want to edit 3 or 5 or 10 conditions if the required columns change later on.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
How about
Excel Formula:
=AND($A1<>"",B1="")
 
Upvote 0
OK that works better, but what if that column selection jumps around? Like COL C, D, I, J, K?

It looks like =AND($A1<>"",B1="") is looking to the left and making the evaluation?

1709316588127.png
 
Upvote 0
OK it looks like you can trick the evaluation to take place on the specific column, but you have to calculate that into the formula and base the second AND argument off of the first column with the requirement.

So if B is the first column, then =AND($A1<>"",B1="") is good.

But if C is the first column, it should be =AND($A1<>"",C1="")

1709317121250.png
 
Upvote 0
Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20236) 64-bit
 
Upvote 0
The 2nd range needs to reflect the first cell in the applies to range.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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