VBA | Comparing Two Cell Values on Separate Sheets, Then Iterating by Row

ssargent40

New Member
Joined
May 26, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am trying to figure out how I can see if one cell is equal to another one on a different sheet, perform an action once that IF statement has been determined, and then iterate down one row on both sheets to perform the same analysis on the cells in the row directly below.

Essentially what I am trying to do is check to see if the values show up on the single data tab the same as they show up on the consolidated tab, and if they do then highlight the cell on the consolidated tab.

The below code tests if the two cells values are equal and if so it highlights the cell on the consolidated data tab. This has been successful but my attempts to loop this functionality have not. Also important to note that the two columns have different row numbers, with there being 59 rows in column H and 14 in column E that have data. Ideally I'd like it to check every value in column H against every value in column E and highlight all the corresponding values that show up in both column H and E on the consolidated tab in column H.

I have a feeling my entire logical approach to this is off, but after a large amount of internet surfing I have yet to understand where I should start . I am quite new to this so any and all input is appreciated, thank you kindly.
If Sheets("2022 Data Submittal").Range("H6").Value = Sheets("Asia Pacific Request").Range("E3").Value Then
Sheets("2022 Data Submittal").Range("H6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi and welcome to MrExcel!

Try this:

VBA Code:
Sub compareCells()
  Dim sh1 As Worksheet
  Dim c As Range, f As Range
  
  Set sh1 = Sheets("2022 Data Submittal")
  
  'For each cell in column H, starting at H6 and up to the last cell with data
  For Each c In sh1.Range("H6", sh1.Range("H" & Rows.Count).End(3))
    'Find the value in column E
    Set f = Sheets("Asia Pacific Request").Range("E:E").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      'if the variable 'f' is not empty it means that found it
      c.Interior.Color = 65535
    End If
  Next
End Sub
 
Upvote 0
Solution
Hi and welcome to MrExcel!

Try this:

VBA Code:
Sub compareCells()
  Dim sh1 As Worksheet
  Dim c As Range, f As Range
 
  Set sh1 = Sheets("2022 Data Submittal")
 
  'For each cell in column H, starting at H6 and up to the last cell with data
  For Each c In sh1.Range("H6", sh1.Range("H" & Rows.Count).End(3))
    'Find the value in column E
    Set f = Sheets("Asia Pacific Request").Range("E:E").Find(c.Value, , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      'if the variable 'f' is not empty it means that found it
      c.Interior.Color = 65535
    End If
  Next
End Sub
This was great, thank you!
 
Upvote 0

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

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