Conditional Formatting with Self-Reference to ISBLANK

[speth]

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.

 

[Fluff]

How about

=AND($A1<>"",B1="")

 

[speth]

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?

 

[speth]

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

 

[awoohaw]

What version of excel do you have?

 

[speth]

Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20236) 64-bit

 

[Fluff]

The 2nd range needs to reflect the first cell in the applies to range.