Compare Values and highlight the unmatched cell value

[Livio]

I need your help, I have the code below:
And what i want to do is:

--Worksheet "A" Column A: (A1)-Alfred (A2)-Charles
--Worksheet "A" Column F: (F1)-Sweden (F2)- France (So the cell value with "France" on the column F is highlighted because is different on Worksheet "B" Column H for Column A with the cell value "Charles")

--Worksheet "B" Column A: (A1)-Alfred (A2)-Charles
--Worksheet "B" Column H: (H1)-Sweden (H2)-Spain

Is not working, can you help me what is wrong?

I want this macro runs automatically on the worksheet "A", if should be Private Sub how to do to run automatically?

Sub missingvalue()

Application.ScreenUpdating = False
Dim desWS As Worksheet, srcWS As Worksheet
Dim strfind As String
Dim LastRow As Long, lngRow As Long, lngDest As Long
Set srcWS = Sheets("Sheet B")
Set desWS = Sheets("Sheet A")

LastRow = srcWS.Range("A1").End(xlDown).Row
For lngRow = 2 To LastRow
strfind = srcWS.Cells(lngRow, 1)
If Not desWS.Columns(1).Find(what:=strfind, lookat:=xlWhole) Is Nothing Then
lngDest = desWS.Columns(1).Find(what:=strfind, lookat:=xlWhole).Row
End If
If desWS.Cells(lngDest, 6).Value <> srcWS.Cells(lngRow, 8).Value Then
desWS.Cells(lngDest, 6).Interior.Color = vbRed
End If
Next lngRow

Application.ScreenUpdating = True

End Sub

 

[Livio]

So, I want to compare the column F of all "A" Worksheets a with column H from the Worksheet "B" and if the values are different on the cells of the column F from all Worksheet "A" that must be highlighted.
Worksheet "B" is like a old database
Worksheet "A" is filtered values from a new database (updated version) lets considered that new database as worksheet "C" ( worksheet "C" is not relevant for this case)

the headers on all the "A" worksheets are on the row 5 and headers of the worksheet "B" are on the row 1

 

[mumps]

Delete the previous macro. Right click the tab name for Sheet B and click 'View Code'. Paste the macro below into the empty code window that opens up. Close the code window to return to your sheet. Activate any other sheet and then activate Sheet B.

Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, arr1 As Variant, arr2 As Variant, Val As String, dic As Object, i As Long, ws As Worksheet
    Set srcWS = Sheets("Sheet B")
    arr1 = srcWS.Range("A1", srcWS.Range("A" & Rows.Count).End(xlUp)).Resize(, 8).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(arr1, 1)
        Val = arr1(i, 1) & "|" & arr1(i, 8)
        If Not dic.Exists(Val) Then
            dic.Add Val, Nothing
        End If
    Next i
    For Each ws In Sheets
        If ws.Name <> "B" And ws.Name <> "C" Then
            ws.Columns("F").Interior.ColorIndex = xlNone
            arr2 = ws.Range("A1", ws.Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
            For i = 1 To UBound(arr2, 1)
                Val = arr2(i, 1) & "|" & arr2(i, 6)
                If Not dic.Exists(Val) Then
                    ws.Range("F" & i).Interior.ColorIndex = 3
                End If
            Next i
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Livio]

Is almost perfect, i mention that also i have worksheet "C" and now even the column F there is hilighted and it shouldnt, is possible to place bounderies?

 

[mumps]

That should not be happening. This line of code excludes Sheet B and Sheet C.

If ws.Name <> "B" And ws.Name <> "C" Then

 

[tusharm]

This is easily solved with a formula. No need for code.

For greater transparency, show the result in a separate column and use that new column for conditional formatting. Otherwise, just use the formula in a c.f. I tested both options.

In my case, the source was Sheet2 and the dest was Sheet3. The data were in A3:B7 in both sheets with row 3 as the header.
Now, in Sheet3, in C4 enter the formula =B4<>XLOOKUP(A4,Sheet2!$A$4:$A$7,Sheet2!$B$4:$B$7,-1,0,1) and copy it down to C5:C7
Conditionally format B{n} based on the value in C{n}

Alternatively, select Sheet3 B4:B7 and conditionally format using the custom formula above.

You can make things even simpler by making the ranges in Sheet2 and Sheet3 as an Excel table. Then, Excel with automagically update the contents of Sheet3 column C based on how many rows you have in Sheet2 and Sheet3. Zero maintenance and complete transparency. My tables had headers named Col1 and Col2. So, the formula becomes
=[@Col2]<>XLOOKUP([@Col1],Table1[Col1],Table1[Col2],-1,0,1)