Forum Discussion

kh78649's avatar
kh78649
Copper Contributor
Jan 21, 2024

Conditional formatting working on one row but not whole sheet

In the below example, in row 3 I want the cell in column A to highlight if the cells E, F, and G are blank. 

 

I can make it work for a single row:

=AND(ISTEXT($A$4),(ISBLANK($E$4:$G:$4)))

 

But not if I try to apply it to the sheet:

=AND(ISTEXT($A:$A),(ISBLANK($E:$G)))

 

I also tried to narrow it down to make sure rows 1 and 2 weren't causing problems:

=AND(ISTEXT($A$3:$A$4),(ISBLANK($E$3:$G$4)))

 

And based on other help articles, I tried removing all $, and just the $ before the row number, no change. 

 

How can I conditionally format my sheet so that in any given row, if there is text in column A and columns E, F, and G are blank, the cell in column A is highlighted? 

 

Thank you!

excel
Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 21, 2024

    kh78649 

    Select A3 and down as far as you want.

    A3 should be the active cell in the selection.

    Delete existing conditional formatting rules, if any.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

    =AND($A3<>"", $E3:$G3="")

    Note that the formula refers to the top row of the selection, and that there are no $ signs before the row number 3.

    Click Format...
    Activate the Fill tab.
    Select a highlight color.
    Click OK, then click OK again.

Resources