2

I am trying to create an equation for conditional formatting that would highlight a cell that is blank if another cell in the same row is not blank (i.e. that row is being "used"). In the picture below, you can see that cell J13 is highlighted red, because it is blank, and cell H13 is not. I have applied the following conditional formatting equation to the range J13:J

=AND(NOT(ISBLANK(H3:H)),(ISBLANK(J3:J)))

Instead of applying a similar equation to each column, I would like an equation that would do this for every cell in the range I3:AA, checking whether each cell in this range is blank, and checking to see if the corresponding cell in column H is not blank. I thought something like this would work over the range I3:AA

=AND(NOT(ISBLANK(H3:H)),(ISBLANK(I3:AA)))

but it unfortunately doesn't work.

Can this even work with conditional formatting? Any help would be great!

Conditional Formatting Example

  • Welcome to Webapps. Please remember that as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. – marikamitsos Oct 24 at 0:40
1

The formula you need is the following:

=NOT(ISBLANK($H3))*(ISBLANK(I3))

Please pay attention to the $ sign in front of H3.
Using the $ (dollar) sign we "lock" the column and the formatting is being applied on the whole row.
You apply the formula on the range I3:AA.

  • I see! I knew there was something funky about how referencing worked with conditional formatting, but I totally forgot how it worked. Thanks marikamitsos! – Clayton Oct 25 at 16:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.