Change the Cell Color based on 4 Cells value in Excel

Question

I have following table and I want to build a conditional formatting or Macro to highlight the cell value column A if following criteria is not meeting..

if all column values are Yes > all good.. no action required. if Column B, C or D value is "No"; check Column A Cell value for "NO"; if it is different, change the background color of Column A Cell value as red.

similarly, if Column B,C, and Column Values are "Yes" but Column A cell value is "NO"; change the background color of Column A cell value as Red.

here is an example:

StablePlan  CaseCount   StockCount  TotalCount
Yes         Yes         Yes         Yes
Yes         Yes         No          Yes
No          Yes         Yes         Yes
No          Yes         Yes         No
Yes         Yes         Yes         Yes

Currently using (but not working):

=OR($A3="NO",$B3="No",$C3="No",$D3="No")

Yes.. I did.. I have tried with conditional formatting but I could give only one rule to highlight if there is a value of "No" in these 4 columns.. it is highlighting the entire row.. — Aamer
Why are you even bothering with conditional formatting let alone a macro? The proper COUNTIF formula in column would guarantee the results. — user4039065

Unknown Unknown · Accepted Answer · 2017-03-23T14:00:57

If you must have a red alert cell in column A, create a conditional formatting rule based upon the following formula.

=OR(AND($A2="yes", COUNTIF($B2:$D2, "no")), AND($A2="no", COUNTIF($B2:$D2, "yes")=3))

However, it seems to me that the correct formula in column A would guarantee the results. Put this in A2,

=IF(COUNTIF(B2:D2, "yes")=3, "Yes", "No")

Fill down. It seems to me that you will never get an incorrect response result in column A.

Change the Cell Color based on 4 Cells value in Excel

1 Answers