Excel VBA: Range match with conditions

Question

Hi there I am pretty new to VBA and any direction would be appreciated.

I have started to write a function to check if an ID entered in a column already exists in a table. So far I think I have a function that compares a range of values (ID's) and highlights the value/displays message box if it already exists.

What I would like to add is two conditions before this is flagged. So....

So basically

Step 1: I want to match value from range column "B" to another range, "Match" (sheet) column B
Step 2: then if columns value "" = "Match".column "E" or "f" = "Match".column "F"
Step 3: then continue with the notification/highlight

Hope that makes sense.....struggling trying to pull the corresponding column E/F value from the original range matches

Much appreciated, Chris

Sub RefCheck() 
    Dim sh As Worksheet, 
    lr As Long, 
    c As Range 

    Set sh = Sheets("sheet1") 'Upload sheet name
    Set st = Sheets("sheet2") 'DB data Edit sheet name

    lr = st.Cells(Rows.Count, "B").End(xlUp).Row 

    For Each c In sh.Range("B7:B" & sh.Cells(Rows.Count, "B").End(xlUp).Row) 
        Set dval = st.Range("B2:B" & lr).Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole) 

         'Add in additional matching criteria here

         'Flag values in range that exists in DB Range (above), if the "ID" value in upload sheet Column "E" ='s the "ID" in DB data sheet Column "E"
         'or "Name" in upload sheet Column "F" ='s the "Name" Column "F" in the DB sheet THEN colour and msgbox


        If Not daval Is Nothing Then 
            c.Interior.ColorIndex = 3 
            my_Alarm = MsgBox("Reference already exists, B" & c.Row, vbExclamation, "Validation Error") 
        End If 

    Next 
End Sub

Romain R · Accepted Answer · 2017-09-14 11:54:56Z

1

I don't know if this is your problem, but you test daval and you set previously dval

If Not dval Is Nothing Then 
        c.Interior.ColorIndex = 3 
        my_Alarm = MsgBox("Well Reference already exists, B" & c.Row, vbExclamation, "Validation Error") 
    End If

answered Sep 14, 2017 at 11:54

Romain R

478 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KacireeSoftware Over a year ago

A good example of how Option Explicit can help avoid wasting time troubleshooting typos!

Collectives™ on Stack Overflow

Excel VBA: Range match with conditions

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related