VBA CODE that checks if two cells are matched

tzcoding

New Member
Joined
Mar 17, 2023
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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).

VBA Code:
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


2023-04-05 09_50_33-Start.png
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Thanks for posting on the forum.

Try this:

VBA Code:
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
--------------
 
Upvote 0
Thanks for posting on the forum.

Try this:

VBA Code:
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.
 
Upvote 0
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.
VBA Code:
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
--------------
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top