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

[ssargent40]

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

 

[DanteAmor]

Hi and welcome to MrExcel!

Try this:

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

 

[ssargent40]

Hi and welcome to MrExcel!

Try this:

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!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.