Conditional Formatting: Column with blank cell to have colour based on value from another column

[atisyam]

Hello

Basically, my question is that if one column has a blank cell AND another column within the same row contained any text except for B&G, then the blank cell will be highlighted (any color).

So based on the figure below: B1 will not be highlighted but both B2 and B3 will be highlighted.

A	B	C
1		B&G
2		B&G&C
3		A&G

<tbody>
</tbody>

Best if the functions used are any available from excel except VBA macro code due to different worksheet formatting. :/ Thank youuu.

 

[Saba Sabaratnam]

Hi

Select cell B2

Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format

Enter the following formula

=IF(AND(A2="",C2="B&G"),TRUE,FALSE) in Format values when this formula is true box

Click on format and select a color for Fill

Close the dialog boxes

Copy down the format from B2 to other cells in Column B

It should work.

Kind regards

Saba

 

[atisyam]

Hi

Select cell B2

Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format

Enter the following formula

=IF(AND(A2="",C2="B&G"),TRUE,FALSE) in Format values when this formula is true box

Click on format and select a color for Fill

Close the dialog boxes

Copy down the format from B2 to other cells in Column B

It should work.

Kind regards

Saba

Hi Saba

Unfortunately, the formula did not work. I've followed as per stated in your steps.

And also, correct me if I'm wrong but it should be =IF(AND(B2="" instead of A2 as the blank column is in column B? Anyways, I tried this too but it did not work.

I will still try to play around with other available functions. Thanks for your effort.

 

[Fluff]

How about
=AND(B1="",C1<>"B&G")
Change B1 & C1 to reflect the first row in the applies to range

 

[atisyam]

How about
=AND(B1="",C1<>"B&G")
Change B1 & C1 to reflect the first row in the applies to range

Hi Fluff. Apologies for the late reply.

It worked as per intended! Thank you for your help in solving this

 

[Fluff]

Glad to help & thanks for the feedback