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]

 

[Trebor76]

Hi atisyam,

Welcome to MrExcell!!

Try this event macro (i.e. has to go on the sheet in question not in a standard separate module):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 4 And Target.Row >= 3 Then
        If Target.Value <> Range("E" & Target.Row - 1) Then
            'Fill cell red if it does not match what's in
            'Col. E for the row above. Change to suit.
            Target.Interior.Color = RGB(255, 0, 0)
        Else
            'Clear any previous colour coding as the S/N's match
            Target.Interior.Color = xlNone
        End If
    End If

End Sub

Hope that helps,

Robert

 

[kalyank854]

Hi , I think this method will not work unless you come out of cell.

Hi atisyam,

Welcome to MrExcell!!

Try this event macro (i.e. has to go on the sheet in question not in a standard separate module):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 4 And Target.Row >= 3 Then
        If Target.Value <> Range("E" & Target.Row - 1) Then
            'Fill cell red if it does not match what's in
            'Col. E for the row above. Change to suit.
            Target.Interior.Color = RGB(255, 0, 0)
        Else
            'Clear any previous colour coding as the S/N's match
            Target.Interior.Color = xlNone
        End If
    End If

End Sub

Hope that helps,

Robert

 

[Trebor76]

Yes, the entry has to be made in the cell (i.e. you can't be in edit mode) before it will work but that's how Excel works. It would be the same even if you did it via Conditional Formatting

 

[atisyam]

Hello Robert/Trebor76

Appreciate the prompt reply. Your code works great, unfortunately, to a certain extent.

Per my example above, once I entered a data in D5 that does not match with E4 with the same vehicle number, the cell auto highlight in red . But if I input the right data (data in D5 and E4 match), the cell still remains red. I've tried to replace xlNone with other no fill/white color online such as xlColorIndexAutomatic or xlColorIndexNone, still no change.

Below is the code that I copy/paste from yours. The changes in 'Target.Column = 6' is because my Old S/N is in column F (the 6th column) and ' ("G" & Target.Row - 1)' is because my new S/N is in column G.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Column = 6 And Target.Row >= 3 Then
If Target.Value <> Range("G" & Target.Row - 1) Then
'Fill cell red if it does not match what's in
'Col. G for the row above. Change to suit.
Target.Interior.Color = RGB(255, 0, 0)
Else
'Clear any previous colour coding as the S/N's match
Target.Interior.Color = xlNone
End If
End If


End Sub



Hope to get an update for this. Thank youuu.

 

[Trebor76]

Not sure as it worked for me

Are 100 percent sure the old and new are the same? Try the EXCAT function on two cells that you believe are the same and see what it produces i.e.

=EXACT(F14,G13)

Will return TRUE if the entry in cells F14 and G13 are the same.

 

[atisyam]

Hello Robert

Did the EXACT function and yep, the old and new are the same. It returns TRUE.

Not sure as it worked for me

Are 100 percent sure the old and new are the same? Try the EXCAT function on two cells that you believe are the same and see what it produces i.e.

=EXACT(F14,G13)

Will return TRUE if the entry in cells F14 and G13 are the same.

 

[Trebor76]

Not sure what's happening then

Try putting a break point at the start of code (by pressing F9), edit a particular cell in Col. F from Row 3 and then step through the code by pressing F8 to try and see what's happening. You know the code only works on a single cell not a range right?

If you still can't solve it perhaps you could host the workbook (devoid of all sensitive data) so we can have a look.

 

[atisyam]

Hmmm, what I understood the code to be was to be able to detect any matching error whenever column E and column D does not match, with respect to the vehicle number. It could occur at any row. In reality, my workbook has hundreds of rows and there are a lot of other columns in place. I wrote the previous posts just as a trial and maybe, it would be best if I were to lay it all out.

I can't post tables through reply so I will start a new thread. I also do not know how to host the workbook. Thanks for all of your help

Not sure what's happening then

Try putting a break point at the start of code (by pressing F9), edit a particular cell in Col. F from Row 3 and then step through the code by pressing F8 to try and see what's happening. You know the code only works on a single cell not a range right?

If you still can't solve it perhaps you could host the workbook (devoid of all sensitive data) so we can have a look.

 

[atisyam]

VBA: Automatically highlight cell when data entered does not match with data in other columns. If re-entered and match, cell turns to 'no fill.'

Hello


I am not a programmer so I apologize if my title seems confusing. This is what I meant: All the vehicle no. are the same. Cells E4 and E6 will auto highlight as the S/N is incorrect, with respect to the sticker location. Cell E5 will not auto highlight as the S/N is correct, with respect to the sticker location.

Then for E4, if the user re-entered '3846', the cell would turn to 'no fill'/clear the cell. For E6, it's not applicable for this example as there are no prior records.


[TABLE="class: grid, width: 499"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Reg. No.
[/TD]
[TD]Date
[/TD]
[TD]Vehicle No.
[/TD]
[TD]Old S/N
[/TD]
[TD]New S/N
[/TD]
[TD]Sticker Location
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]01
[/TD]
[TD]12/1/18
[/TD]
[TD]SG9876
[/TD]
[TD]-
[/TD]
[TD]3846
[/TD]
[TD]Window
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]02
[/TD]
[TD]12/1/18
[/TD]
[TD]SG9876
[/TD]
[TD]-
[/TD]
[TD]3847
[/TD]
[TD]Door
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]03
[/TD]
[TD]13/2/18
[/TD]
[TD]SG9876
[/TD]
[TD]3847
[/TD]
[TD]4180
[/TD]
[TD]Window
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]04
[/TD]
[TD]13/2/18
[/TD]
[TD]SG9876
[/TD]
[TD]3847
[/TD]
[TD]4181
[/TD]
[TD]Door
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]05
[/TD]
[TD]14/2/18
[/TD]
[TD]SG9876
[/TD]
[TD]4180
[/TD]
[TD]4182
[/TD]
[TD]Side Panel
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]06
[/TD]
[TD]14/2/18
[/TD]
[TD]SG9876
[/TD]
[TD]4181
[/TD]
[TD]4183
[/TD]
[TD]Door
[/TD]
[/TR]
</tbody>[/TABLE]



Hope that someone could help me out. The VBA code that I hoped for is that the formula is applicable for all rows (in reality I have 700+ rows), but the columns are fixed to these 3 columns (E, F & G). Thank you so much!