Conditional formatting not working properly as one of the comparing cells contains a vlookup formula

%3CLINGO-SUB%20id%3D%22lingo-sub-211036%22%20slang%3D%22en-US%22%3EConditional%20formatting%20not%20working%20properly%20as%20one%20of%20the%20comparing%20cells%20contains%20a%20vlookup%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-211036%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20problems%20with%20a%20simple%20conditional%20formatting%20as%20one%20of%20the%20comparing%20values%20is%20a%20vlookup%20formula%20and%20not%20a%20number.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20the%20comparison%20with%20another%20set%20of%20data%20containing%20just%20numbers%20and%20not%20values%20and%20it%20works%20perfect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20let%20the%20conditional%20format%20know%20that%20we%20need%20to%20compare%20the%20values%20of%20column%20A%20with%20the%20values%20of%20column%20B%20which%20gets%20filled%20with%20a%20VLookup%20formula%20and%20then%20highlight%20the%20cells%20containing%20different%20values%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20pictures.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20460px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36938i036735DF7481F272%2Fimage-dimensions%2F460x261%3Fv%3D1.0%22%20width%3D%22460%22%20height%3D%22261%22%20alt%3D%222.PNG%22%20title%3D%222.PNG%22%20%2F%3E%3C%2FSPAN%3EIn%20this%20case%2C%20the%20conditional%20format%20is%20working%20as%20Column%20Brand%20has%20been%20input%20manually%20typing%20the%20numbers%20and%20the%20red%20cells%20are%20those%20ones%20showing%20differences%20between%20Column%20Inventory%20and%20Column%20Brand.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20463px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36939i31A471B59662EA58%2Fimage-dimensions%2F463x262%3Fv%3D1.0%22%20width%3D%22463%22%20height%3D%22262%22%20alt%3D%221.PNG%22%20title%3D%221.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%3EIn%20this%20case%2C%20the%20conditional%20format%20is%20not%20working.%20Column%20Available%20inventory%20is%20filled%20as%20a%20result%20of%20a%20Vlookup%20formula.%20Column%20Inventory%20and%20Column%20Avail.%20Inv.%20are%20all%20red%20for%20some%20reason.%20I%20think%20this%20is%20because%20when%20the%20conditional%20formatting%20compares%20Columns%20Inventory%20with%20Avail%2C%20everything%20is%20different%20as%20Column%20Avail's%20cells%20contain%20a%20formula%20and%20not%20a%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20comparing%20formula%20for%20the%20conditional%20format%20is%20very%20easy%26nbsp%3B%3D%24B2%26lt%3B%26gt%3B%24H2%20(Fill%20with%20red%20all%20cells%20from%20rows%20B%20and%20H%20with%20values%20are%20different%20from%20each%20other).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20any%20one%20have%20an%20idea%20about%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20any%20one%20have%20had%20the%20same%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20knows%20how%20to%20solve%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOdin.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-211036%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%202016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Evlookup%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-211093%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20not%20working%20properly%20as%20one%20of%20the%20comparing%20cells%20contains%20a%20vlookup%20for%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-211093%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Odin%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConditional%20formatting%2C%20as%20any%20other%20formatting%2C%20definitely%20doesn't%20depend%20on%20how%20the%20cell%20receives%20its%20value%2C%20by%20formula%20or%20it%20was%20entered%20manually.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%2C%20the%20formula%20could%20return%20not%20what%20we%20assume%20(e.g.%20text%20with%20some%20non-visible%20characters).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20point%20is%20formatting.%20It%20looks%20like%20on%20your%20first%20screenshot%20both%20columns%20are%20formatted%20as%20text%20(green%20triangle%20on%20top%20left%20of%20each%20cell).%20For%20the%26nbsp%3Bnext%20screenshot%20second%20column%20is%20not%20text%20(otherwise%20formula%20doesn't%20work)%20and%20you%20compare%20text%20in%20first%20column%20with%20number%20in%20second%20one.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20something%20else%2C%20without%20s%3CSPAN%3Eample%20file%20attached%20with%20non-sensitive%20information%20%3C%2FSPAN%3Ewe%20may%20discuss%20forever%20what%20could%20be%20possible%20reasons.%20But%20you%20my%26nbsp%3Btry%20one%20by%20one%2C%20start%20from%20Wyn's%20suggestion%2C%20most%20probably%20it%20works.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-211088%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20not%20working%20properly%20as%20one%20of%20the%20comparing%20cells%20contains%20a%20vlookup%20for%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-211088%22%20slang%3D%22en-US%22%3EHi%20Odin%2C%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20could%20try%20%3DVALUE(%24B2)%20%26lt%3B%26gt%3B%20VALUE(%24G2)%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi,

 

I'm having problems with a simple conditional formatting as one of the comparing values is a vlookup formula and not a number. 

 

I did the comparison with another set of data containing just numbers and not values and it works perfect.

 

 

Is there any way to let the conditional format know that we need to compare the values of column A with the values of column B which gets filled with a VLookup formula and then highlight the cells containing different values?

 

Please see the pictures.


2.PNGIn this case, the conditional format is working as Column Brand has been input manually typing the numbers and the red cells are those ones showing differences between Column Inventory and Column Brand.1.PNG

 

 

 

In this case, the conditional format is not working. Column Available inventory is filled as a result of a Vlookup formula. Column Inventory and Column Avail. Inv. are all red for some reason. I think this is because when the conditional formatting compares Columns Inventory with Avail, everything is different as Column Avail's cells contain a formula and not a value.

 

My comparing formula for the conditional format is very easy =$B2<>$H2 (Fill with red all cells from rows B and H with values are different from each other).

 

Does any one have an idea about this?

 

Does any one have had the same problem?

 

Does anyone knows how to solve this?

 

Thank you.

 

Odin.

 

2 Replies
Highlighted
Hi Odin,

You could try =VALUE($B2) <> VALUE($G2)
Highlighted

Hi Odin,

 

Conditional formatting, as any other formatting, definitely doesn't depend on how the cell receives its value, by formula or it was entered manually.

 

However, the formula could return not what we assume (e.g. text with some non-visible characters).

 

Another point is formatting. It looks like on your first screenshot both columns are formatted as text (green triangle on top left of each cell). For the next screenshot second column is not text (otherwise formula doesn't work) and you compare text in first column with number in second one.

 

That could be something else, without sample file attached with non-sensitive information we may discuss forever what could be possible reasons. But you my try one by one, start from Wyn's suggestion, most probably it works.