Conditional formatting based on two merged cells content

[Steve Jobs]

Hello,

I have two Merged cells with values Cell B8 = 50, 31, 17, 86, 43 and Cell B10 = 50, 31, 86, 43, 41. Now I need a formula or VBA code to highlight another Cell D8 with Red if there is any one common value in both the cells. In this case, 50 and 31 are matching values. I tried using match but does not worked as the cells are merged.

Thanks!

 

[Rijnsent]

Hi Steve,

what works for me is an array formula. This assumes maximum 5 numbers in every cell, 2 digits per number, comma & space separated. The formula could be made more flexible, but if your data isn't any more complex, this will do the trick:

{=MAX(IF(ISERROR(SEARCH(IF(MID(G$3;{1;5;9;13;17};2)<>"";MID(G$3;{1;5;9;13;17};2);"x");$F4));0;1))}

The two cells to compare are G3 and F4. The formula basically cuts the value of G3 into 2 digit chunks (starting at position 1,5,9,13 and 17) and searches for that string in the other cell (F4). It returns a 0 if it's not found and a 1 if it's found. It's an array formula, so you'd have to press CTRL+SHIFT+ENTER to get it to work. My formula has ; as a separator, it might be that you'd have to change that for commas.
Cheers,
Koen