VBA: Automatically highlight cell when user enter data that does not match with another data from a different column

[atisyam]

Hello

I am not a programmer so I apologize if my title seems confusing. This is what I meant: Cell D5 will automatically highlight as the data entered does not match with E4 for the same vehicle number. Cell D3 will not be highlighted as the data entered matched with E2 for the same vehicle number. Hope it is possible. Thanks to anyone who can help.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Inspection date[/TD]
[TD]Vehicle Number[/TD]
[TD]Old S/N[/TD]
[TD]New S/N[/TD]
[TD]Reason[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10/1/18[/TD]
[TD]Sm1234[/TD]
[TD]-[/TD]
[TD]098[/TD]
[TD]Bus Arrival[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10/2/18[/TD]
[TD]Sm1234[/TD]
[TD]098[/TD]
[TD]345[/TD]
[TD]Maintenance[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12/2/18[/TD]
[TD]Sm5678[/TD]
[TD]-[/TD]
[TD]213[/TD]
[TD]Bus Arrival[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12/3/18[/TD]
[TD]Sm5678[/TD]
[TD]345[/TD]
[TD]639[/TD]
[TD]Maintenance[/TD]
[/TR]
</tbody>[/TABLE]

 

[atisyam]

Hello again Trebor 76

Just to ask, is this code is only applicable for existing information on a sheet? i.e. to highlight the errors to enable individual to visualize the mistake and amend accordingly.

But if I were to create a new row, say continue from E8 onwards, even when I put the correct sticker location first then input the correct "old S/N" (i.e. E8 = 4183. Sticker location = Door), the cell E8 will be in red with the popups.

Super embarrassing but yeah, must the following code be changed to accommodate for any new rows?

Is this what you're after:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngMyCell As Range
    Dim dblOldSN As Double
    Dim strStickerLocation As String
    
    Application.ScreenUpdating = False
    
    If Target.Column = 6 And Target.Row >= 3 And Len(Target.Value) > 0 Then
        dblOldSN = Range("F" & Target.Row)
        strStickerLocation = Range("H" & Target.Row)
        For Each rngMyCell In Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row)
            'Assumes there is only one S/N match in Col. G.
            If rngMyCell = dblOldSN Then
                If rngMyCell.Offset(0, 1) = strStickerLocation Then
                    Target.Interior.Color = xlNone
                    Exit For
                End If
            Else
                Target.Interior.Color = RGB(255, 0, 0)
                If MsgBox("Serial No. does not match with previous record. Continue?", vbRetryCancel, "Incorrect Serial No.") = vbRetry Then
                    Target.Select
                    Exit For
                Else
                    MsgBox "Red cell will denote as error", vbInformation
                    Exit For
                End If
            End If
        Next rngMyCell
    End If

    Application.ScreenUpdating = True

End Sub

Please use the correct tags i.e. [CODE] Code goes here [/CODE] when posting code.

Robert

 

[Trebor76]

The code will only work on the sheet it's attached to. It works when a cell in column F from Row 3 is changed (you changed it from column E to F ). Once a change in any cell from column F from Row 3 has been changed the code works down column G from G3 to whatever the last row is in column G - no change in the code required.

 

[atisyam]

Noted. Great, thanks for the explanation Trebor76!