How to highlight row in excel when no specific texts are on specific columns-CodePudding

I have a table in Excel

Column1    Column2   Column3
1          Apple       $5
2          Banana      $1
3          Kiwi
4          Banana
5          Apple
6          Orange      $4

I want to add a new conditional formatting rule where Column2 does not equal to Banana OR orange , AND Column3 is empty. Rows to be highlighted:

Column1    Column2   Column3
   3          Kiwi
   5          Apple

I selected the table, clicked New Rule and used formula below but not working. What is the correct syntax? Thanks.

=($B$!="Banana" & $B$!="Orange") | $C$ = null)

CodePudding user response：

When entering conditional formatting that you want to apply to multiple cells, uses relative references.
For example, you could enter the following at Column2 (B), Row 1, and then copy the formatting down the column:

=AND(B1<>"Banana",B1<>"Orange",C1="")

Alternately: Select the Column (with B1 active) and Add/Edit the conditional. It will be applied to all, with correct relative references for each cell.

CodePudding user response：

Use this as the rule:

=AND($B1<>"Banana" ,$B1<>"Orange",$C1="",$A1<>"")

And apply it to the full column of $A:$C: