Formula in macro dont work with mixed number and letters

HI Below code compare the value in part of column P with the actual value in column H. If any difference it mark in column H.
It works fine when all is numbers. But in some cases it can be mixed and here it give the wrong result.

In the test sheet all ís matching according to the formula in the code. But it mark H12 to H15 yellow, because I think there is mixed letters and numbers.
The formula check from digit 15 and 11 digit with the mid function and then it add up to 12 digets with leading zeroes.

Here below is the code. and there is also a test sheet to check with.

Please have a look

Sincerely
Abjac

Sub inputupdated20()
'this is working to compare and mark yellow for all

Sheets(1).Activate
Range("A65536").Select
Selection.End(xlUp).Select
Let x = ActiveCell.Row

Range("H9").Select
Do Until ActiveCell.Row > x
    Let a = Format(ActiveCell.Value, "000000000000")
    ActiveCell.FormulaR1C1 = "=TEXT(MID(RC[8],15,11),""000000000000"")"
    ActiveCell.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Let b = Format(ActiveCell.Value, "000000000000")
    Let c = ActiveCell.Row
    If a <> b Then
        ActiveCell.Value = a
        Selection.Interior.ColorIndex = 6
        Range("H" & c & "").Select
    End If
    ActiveCell.Offset(1, 0).Select
Loop
    
End Sub

Try something like:

Sub inputupdated20_rag()
Dim count
With Sheets(1)
    For count = 9 To .Range("A" & Rows.count).End(xlUp).Row
        If .Range("H" & count) <> "0" & Strings.Mid(.Range("P" & count), 15, 11) Then
            .Range("H" & count).Interior.ColorIndex = 6
        End If
    Next count
End With
End Sub

Wow ragulduy it works really great and much better for me to use in fact. One question. Can I use this in also right formulas and how will that look.

Thanks

Abjac

Sorry, I'm not sure I can understand the question..

You could use a conditional format like:
=H9<>"0"&MID(P9,15,11)
to highlight column H as necessary?

I mean if I wanted to make this code like urs. Its right function in the formula.
Sub inputupdated20()
This one would check the same way but in column I and for the last 2 digits in column P

Sheets(1).Activate
Range("A65536").Select
Selection.End(xlUp).Select
Let x = ActiveCell.Row


Range("I9").Select
Do Until ActiveCell.Row > x
    Let a = Format(ActiveCell.Value, "00")
    ActiveCell.FormulaR1C1 = "=TEXT(RIGHT(RC[7],2),""00"")"
    ActiveCell.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Let b = Format(ActiveCell.Value, "00")
    Let c = ActiveCell.Row
    If a <> b Then
        ActiveCell.Value = a
        Selection.Interior.ColorIndex = 6
        Range("I" & c & "").Select
    End If
    ActiveCell.Offset(1, 0).Select
Loop
Range("A1").Select


   
End Sub

Oh ok, yes the equivalent would be:

Sub inputupdated20_rag()
Dim count
With Sheets(1)
    For count = 9 To .Range("A" & Rows.count).End(xlUp).Row
        If .Range("I" & count) <> "0" & Strings.Right(.Range("P" & count), 2) Then
            .Range("I" & count).Interior.ColorIndex = 6
        End If
    Next count
End With
End Sub

That's why I asked if you put this in the test sheet it will mark all yellow. It will check the last 2 digits and if any difference it have to be yellow. But here it mark all in column I. Don't understand why its like that. Try check this in the test sheet for column I.

Thanks in advance

Abjac

If I put this one in for column F it does the same mark all yellow. Don't understand why its working only for column H.
This is the code for column F . Abjac

Sub inputupdated20_rag2()
Dim count
With Sheets(1)
    For count = 9 To .Range("A" & Rows.count).End(xlUp).Row
        If .Range("F" & count) <> "0" & Strings.Mid(.Range("P" & count), 5, 5) Then
            .Range("F" & count).Interior.ColorIndex = 6
        End If
    Next count
End With
End Sub

Sorry, I forgot to remove the "0" & which isn't necessary for the other columns:

Sub inputupdated20_rag()
Dim count
With Sheets(1)
    For count = 9 To .Range("A" & Rows.count).End(xlUp).Row
        If .Range("H" & count) <> "0" & Strings.Mid(.Range("P" & count), 15, 11) Then
            .Range("H" & count).Interior.ColorIndex = 6
        End If
        If .Range("I" & count) <> Strings.Right(.Range("P" & count), 2) Then
            .Range("I" & count).Interior.ColorIndex = 6
        End If
        If .Range("F" & count) <> Strings.Mid(.Range("P" & count), 5, 5) Then
            .Range("F" & count).Interior.ColorIndex = 6
        End If
    Next count
End With
End Sub

It was working fine and I though I knew the system so I added the last column but in column G it again mark all cells. There is leading zeroes in this column,, My code was like this..
Cant see again why its not working. Have a look and try below code.
Its with all the columns I need to check.
Abjac

Sub inputupdated20_ragworking()
Dim count
With Sheets(1)
    For count = 9 To .Range("A" & Rows.count).End(xlUp).Row
        
        If .Range("F" & count) <> Strings.Mid(.Range("P" & count), 5, 5) Then
            .Range("F" & count).Interior.ColorIndex = 6
        End If
         If .Range("G" & count) <> Strings.Mid(.Range("P" & count), 10, 5) Then
            .Range("G" & count).Interior.ColorIndex = 6
        End If
        
        If .Range("H" & count) <> "0" & Strings.Mid(.Range("P" & count), 15, 11) Then
            .Range("H" & count).Interior.ColorIndex = 6
        End If
        If .Range("I" & count) <> Strings.Right(.Range("P" & count), 2) Then
            .Range("I" & count).Interior.ColorIndex = 6
        End If
        
    Next count
End With
End Sub

Try:

Sub inputupdated20_ragworking()
Dim count
With Sheets(1)
    For count = 9 To .Range("A" & Rows.count).End(xlUp).Row
        
        If .Range("F" & count) <> Strings.Mid(.Range("P" & count), 5, 5) Then
            .Range("F" & count).Interior.ColorIndex = 6
        End If
         If .Range("G" & count) <> "000" & Strings.Mid(.Range("P" & count), 10, 5) Then
            .Range("G" & count).Interior.ColorIndex = 6
        End If
        
        If .Range("H" & count) <> "0" & Strings.Mid(.Range("P" & count), 15, 11) Then
            .Range("H" & count).Interior.ColorIndex = 6
        End If
        If .Range("I" & count) <> Strings.Right(.Range("P" & count), 2) Then
            .Range("I" & count).Interior.ColorIndex = 6
        End If
        
    Next count
End With
End Sub

Hi This is working for sure fine now. Just hard to understand the total logic in it. But its working brilliant.

Thanks for you patience, great help.

Have a nice evening

Abjac