SOLVED

Conditional formatting OR statement

%3CLINGO-SUB%20id%3D%22lingo-sub-3399486%22%20slang%3D%22en-US%22%3EConditional%20formatting%20OR%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3399486%22%20slang%3D%22en-US%22%3E%3CP%3EI%20created%20a%20very%20simple%20formatting%20rule%20however%20I%20can't%20get%20it%20to%20work%20properly.%3C%2FP%3E%3CP%3EI%20want%20the%20cells%20in%20the%20left%20column%20to%20appear%20orange%20if%20they%20don't%20match%20the%20right%20column%20or%20if%20the%20cell%20itself%20contains%20an%20error.%20Somehow%20it%20doesn't%20apply%20the%20error%20condition%2C%20in%20the%20OR%20statement.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBoth%20conditions%20work%20on%20their%20own.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Milliumfolie_1-1652965560068.png%22%20style%3D%22width%3A%20656px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373162iBE73F6DA16297118%2Fimage-dimensions%2F656x424%3Fv%3Dv2%22%20width%3D%22656%22%20height%3D%22424%22%20role%3D%22button%22%20title%3D%22Milliumfolie_1-1652965560068.png%22%20alt%3D%22Milliumfolie_1-1652965560068.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3399486%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3399575%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20OR%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3399575%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1394651%22%20target%3D%22_blank%22%3E%40Milliumfolie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20able%20to%20replicate%20the%20same%20behavior%20you%20were%20seeing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20we%20enter%20the%20formula%20on%20the%20spreadsheet%2C%20we%20can%20see%20the%20OR%20produces%20a%20%23VALUE!%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20using%20Evaluate%20Formula%20from%20the%20Formula%20tab%20of%20the%20ribbon%2C%20we%20can%20see%20this%20is%20because%20the%20%26lt%3B%26gt%3B%20operator%20produces%20a%20%23VALUE!%20error%20when%20either%20of%20the%20operands%20are%20an%20error%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22orerror.gif%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373168i1496108585A13FF0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22orerror.gif%22%20alt%3D%22orerror.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20think%20you%20have%20two%20choices%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Separate%20them%20into%20different%20rules%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22flexyourdata_0-1652966449486.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373167i994079CD3EB9F1A8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22flexyourdata_0-1652966449486.png%22%20alt%3D%22flexyourdata_0-1652966449486.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Use%20this%20instead%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(ISERROR(B2)%2CTRUE%2CB2%26lt%3B%26gt%3BC2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22flexyourdata_1-1652966818082.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F373169i50E135F23EDBFCD9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22flexyourdata_1-1652966818082.png%22%20alt%3D%22flexyourdata_1-1652966818082.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3399681%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20OR%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3399681%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20explanation%20and%20solution!%3C%2FLINGO-BODY%3E
New Contributor

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 contains an error. Somehow it doesn't apply the error condition, in the OR statement.

 

Both conditions work on their own.

 

Milliumfolie_1-1652965560068.png

 

 

 

 

2 Replies
best response confirmed by Milliumfolie (New Contributor)
Solution

@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.

 

orerror.gif

 

So, I think you have two choices:

 

1. Separate them into different rules:

 

flexyourdata_0-1652966449486.png

 

2. Use this instead:

 

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

 

flexyourdata_1-1652966818082.png

 

Thanks for your explanation and solution!