Forum Discussion

Milliumfolie's avatar
Milliumfolie
Copper Contributor
May 19, 2022
Solved

Conditional formatting OR statement

I created a very simple formatting rule however I can't get it to work properly. I want the cells in the left column to appear orange if they don't match the right column or if the cell itself conta...
excel
  • flexyourdata's avatar
    flexyourdata
    May 19, 2022

    Milliumfolie 

     

    I was able to replicate the same behavior you were seeing.

     

    If we enter the formula on the spreadsheet, we can see the OR produces a #VALUE! error.

     

    By using Evaluate Formula from the Formula tab of the ribbon, we can see this is because the <> operator produces a #VALUE! error when either of the operands are an error value.

     

     

    So, I think you have two choices:

     

    1. Separate them into different rules:

     

     

    2. Use this instead:

     

    =IF(ISERROR(B2),TRUE,B2<>C2)

     

     

flexyourdata's avatar
flexyourdata
Iron Contributor
May 19, 2022

Milliumfolie 

 

I was able to replicate the same behavior you were seeing.

 

If we enter the formula on the spreadsheet, we can see the OR produces a #VALUE! error.

 

By using Evaluate Formula from the Formula tab of the ribbon, we can see this is because the <> operator produces a #VALUE! error when either of the operands are an error value.

 

 

So, I think you have two choices:

 

1. Separate them into different rules:

 

 

2. Use this instead:

 

=IF(ISERROR(B2),TRUE,B2<>C2)

 

 

  • Milliumfolie's avatar
    Milliumfolie
    Copper Contributor
    May 19, 2022
    Thanks for your explanation and solution!

Resources