LOOKUP / MATCH - Data Verification Sheet

[dinkss]

Hi All,

I'm trying to get double verification worksheet created but don't know how to start.
Basically there are 3 tabs:
1. DATA_EXTRACT
2. PERFORMER
3. VERIFIER

DATA_EXTRACT tab will be hidden with all information about products etc and users will not be able to see it.

My question is: How can I do a lookup / match formula in two tabs (PERFORMER & VERIFIER), where PERFORMER tab and VERIFIER tab will have data entered by user to see if it is matching data in hidden sheet (DATA_EXTRACT). If data will be entered incorrectly - will not match what's in DATA_EXTRACT tab, user should get a message - warning that DATA ENTERED IS INCORRECT.

Can you help me build a formula / macro with something like that?

 

[dinkss]

You are welcome.
Glad I was able to help!

Hi,

Just one more question - how can I disable Cut Copy Paste option in my workbook? Previous VBA works great but I would also like to have a VBA that would block Cut Copy Paste option in Excel.

How can I do that?

 

[Joe4]

Hi,

Just one more question - how can I disable Cut Copy Paste option in my workbook? Previous VBA works great but I would also like to have a VBA that would block Cut Copy Paste option in Excel.

How can I do that?

That is a whole different question, and therefore should be posted in a new thread.

Note that the question has been asked many time before, so I would recommend doing some Google searches before posting it.

 

[dinkss]

That is a whole different question, and therefore should be posted in a new thread.

Note that the question has been asked many time before, so I would recommend doing some Google searches before posting it.

OK, thank you

 

[dinkss]

You are welcome.
Glad I was able to help!

Can this script be adjusted to change cell colour to yellow or red if data entered is incorrect?
Instead of getting data deleted? This will be very helpful.

 

[Joe4]

Sure, just change the line of code clearing the contents with one that formats that cell with a color, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim ad As String
    
'   Check to see if entry matches DATA_EXTRACT sheet
    For Each cell In Target
'       Get cell address
        ad = cell.Address(0, 0)
'       Compare value to value from same cell on DATA_EXTRACT sheet
'       If not same, clear the value and return message
        If cell.Value <> Sheets("DATA_EXTRACT").Range(ad).Value Then
            Application.EnableEvents = False
            cell.Interior.Color = vbYellow
            Application.EnableEvents = True
            MsgBox "Entry in cell " & ad & " is wrong!", vbOKOnly, "DATA ENTERED IS INCORRECT"
        End If
    Next cell
    
End Sub

 

[dinkss]

Sure, just change the line of code clearing the contents with one that formats that cell with a color, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim ad As String
   
'   Check to see if entry matches DATA_EXTRACT sheet
    For Each cell In Target
'       Get cell address
        ad = cell.Address(0, 0)
'       Compare value to value from same cell on DATA_EXTRACT sheet
'       If not same, clear the value and return message
        If cell.Value <> Sheets("DATA_EXTRACT").Range(ad).Value Then
            Application.EnableEvents = False
            cell.Interior.Color = vbYellow
            Application.EnableEvents = True
            MsgBox "Entry in cell " & ad & " is wrong!", vbOKOnly, "DATA ENTERED IS INCORRECT"
        End If
    Next cell
   
End Sub

This works fantastic, but can this be adjusted to be coloured if data doesn't match but once it is matched to change color back?

Because it will let me put wrong data which is fine, and it is colouing cell fine also but when I enter correct data cell that was coloured stays coloured and I would like it to go back to same colour (or colour of my selection?)

 

[Joe4]

This works fantastic, but can this be adjusted to be coloured if data doesn't match but once it is matched to change color back?

Because it will let me put wrong data which is fine, and it is colouing cell fine also but when I enter correct data cell that was coloured stays coloured and I would like it to go back to same colour (or colour of my selection?)

I feel like I am in a "Catch-22" (every time I provide an answer, the question changes!)

This will remove the color, but note it will remove ANY and ALL color from that cell:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim ad As String
    
'   Check to see if entry matches DATA_EXTRACT sheet
    For Each cell In Target
'       Get cell address
        ad = cell.Address(0, 0)
'       Compare value to value from same cell on DATA_EXTRACT sheet
'       If not same, clear the value and return message
        If cell.Value <> Sheets("DATA_EXTRACT").Range(ad).Value Then
            Application.EnableEvents = False
            cell.Interior.Color = vbYellow
            Application.EnableEvents = True
            MsgBox "Entry in cell " & ad & " is wrong!", vbOKOnly, "DATA ENTERED IS INCORRECT"
        Else
            cell.Interior.Pattern = xlNone
        End If
    Next cell
    
End Sub

 

[dinkss]

I feel like I am in a "Catch-22" (every time I provide an answer, the question changes!)

This will remove the color, but note it will remove ANY and ALL color from that cell:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    Dim ad As String
   
'   Check to see if entry matches DATA_EXTRACT sheet
    For Each cell In Target
'       Get cell address
        ad = cell.Address(0, 0)
'       Compare value to value from same cell on DATA_EXTRACT sheet
'       If not same, clear the value and return message
        If cell.Value <> Sheets("DATA_EXTRACT").Range(ad).Value Then
            Application.EnableEvents = False
            cell.Interior.Color = vbYellow
            Application.EnableEvents = True
            MsgBox "Entry in cell " & ad & " is wrong!", vbOKOnly, "DATA ENTERED IS INCORRECT"
        Else
            cell.Interior.Pattern = xlNone
        End If
    Next cell
   
End Sub

This worked fantastic:
I have changed your code to this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim ad As String

' Check to see if entry matches DATA_EXTRACT sheet
For Each cell In Target
' Get cell address
ad = cell.Address(0, 0)
' Compare value to value from same cell on DATA_EXTRACT sheet
' If not same, clear the value and return message
If cell.Value <> Sheets("DATA_EXTRACT").Range(ad).Value Then
Application.EnableEvents = False
cell.Interior.Color = vbYellow
Application.EnableEvents = True
MsgBox "Entry in cell " & ad & " is wrong!", vbOKOnly, "DATA ENTERED IS INCORRECT"
Else
cell.Interior.Color = RGB()
End If
Next cell

End Sub

and it is working fine.

seems like I have no issues now, thanks a lot, I really appreciate it.