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!

 

[eibi]

I'm confused: 86 and 43 are also common values in both cells. Does the position in the sequence have to match as well as the value? Your original post is unclear.

This CSE (array) formula will test for common values (but not common position):

=IF(SUM(LEN(SUBSTITUTE(B10,TRIM(MID(SUBSTITUTE(B8,",",REPT(" ",256)),N(IF(1,((ROW(1:5))*256)-255)),30)),"")))-(LEN(B10)*5)<0,TRUE, FALSE)

You can paste this in D8 and set up your conditional formatting based on a simple TRUE/FALSE.

BUT. This formula has lots of problems. Maybe it's a starting place? It assumes that you'll always have 5 values (all with 2 digits) to check in both B8 and B10. It won't work if you have long decimals...And it won't work if you have single digit numbers: 5 and 35 will test as a match, for example, so you'll have to use '05'.

It would be helpful to have some of your sample file to really understand the situation.

 

[Nebu]

Hi:

The following code will do what you are looking for

Sub test()
Application.ScreenUpdating = False

Range("D8").ClearContents

strg$ = Range("B8").Text
arr = Split(strg, ",")

For i& = 0 To UBound(arr)
    m$ = Trim(arr(i))
    strg1$ = Range("B10").Text
    arr1 = Split(strg1, ",")
     
        For j& = 0 To UBound(arr1)
            m1$ = Trim(arr1(j))
                If m1 = m Then
                    Range("D8") = True
                    Exit For
                Else
                    Range("D8") = False
                End If
        Next
  If Range("D8") = True Then Exit For
Next

Application.ScreenUpdating = True
End Sub

Note: I have written the code as per the file uploaded by Eibi, You may have to tweak the code as per your original file.

Thanks

 

[Steve Jobs]

Thanks both, the formula worked pretty well. I just want to expand the comparison from 2 cells to 4 say B8,B9,B10,B11. Is there a way to accommodate in the formula. VBA would too lengthy as i have 30 cells to compare in total.

 

[Steve Jobs]

Any input, Instead of 2 cells, i want this to work for 4 cells. If there is any matching value in any of the 4. the formula should be true