VBA CODE that checks if two cells are matched

[tzcoding]

I need help getting this VBA code below to work automatically across multiple cells. I was using this formula before =IF(S6=Q6, "MATCH", "NOT MATCH") and just copying it down to the next cell below, but that's not efficient with a larger data set.

Help Need: I need the VBA code to look at a range from Column S6 to Column Q6933. See if they match and if so output to Column T the results (Match Or Not Match).

Public Sub Shop_ID_Check()

    If Range("S6").Value = Range("Q6").Value Then
        Range("T6").Value = "MATCH"
    Else
        Range("T6").Value = "NOT MATCH"
    End If
    
End Sub

 

[DanteAmor]

Thanks for posting on the forum.

Try this:

Public Sub Shop_ID_Check()
  Dim lr As Long
  lr = Range("Q:S").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  With Range("T6:T" & lr)
    .Formula = "=IF(S6=Q6, ""MATCH"", ""NOT MATCH"")"
    .Value = .Value
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[tzcoding]

Thanks for posting on the forum.

Try this:

Public Sub Shop_ID_Check()
  Dim lr As Long
  lr = Range("Q:S").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  With Range("T6:T" & lr)
    .Formula = "=IF(S6=Q6, ""MATCH"", ""NOT MATCH"")."
    .Value = .Value
  End With
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Thank you This ended up working ... However Is there a way to make it work without having to click run macro or without assigning it to a button. If not not then its fine i'll just make it a button and call it good.

 

[DanteAmor]

However Is there a way to make it work without having to click run macro or without assigning it to a button.

Put the following code in the sheet events where you want this to work.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range
  
  Set rng = Intersect(Target, Range("Q6:Q" & Rows.Count & ",S6:S" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      If Range("Q" & c.Row).Value = Range("S" & c.Row).Value Then
        Range("T" & c.Row).Value = "MATCH"
      Else
        Range("T" & c.Row).Value = "NO MATCH"
      End If
    Next
  End If
End Sub

NOTE SHEET EVENT:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------