Comparing Two Sheets Using Key Field Column

**whitelockben** · 05-08-2012, 04:29 PM

Hi all,

I am trying to write a macro which will allow me to compare two excel sheets based on a column of key fields.

What I have is a workbook containing two sheets. Column A is a column of key fields and data between the key fields in held in columns B:F.

E,g, A1 is the unique key field, B1:F1 contains data relating to the key field.

This key field exists in my secoind sheet but may not be in row 1, e.g. the key field in this example may be A4 (data relating to this key field in B4:F4).

Subsiquently I need something that would start at cell A1 and 'search' through a defined range in column A of my second sheet in my workbook until it finds the matching key field in my first sheet. Then I would need to compare the B:F cells of the relative row and bold and italic any cells in that sheet.

I was hoping some one would be able to point me in the right direction of how this would be achieved or possibly be able to share some code with me that can achieve this or something similar that I would be able to adapt.

I have a small amount of VBA experience but I am trying to pick up as much as possible and have found it easier to learn when I have practicle examples of code to look at. I was thinking this would be achieved using arrays to match the key fields and then if statements like

IF Activecell.row (< active cell being key field) column b <> sheet2.activecell.row ( < active cell being matched key field) column b
set sheet2.activecell.row cell b bold = true

^^^ Above repeated for each column I want comparing. ^^^ I know the syntaxs are not correct (purely for explination).

I could upload an example of what I am trying to achieve if further explination is required.

Thank you in advance,

Ben.

**whitelockben** · 05-10-2012, 06:09 PM

No one with any ideas?

**AlvaroSiza** · 05-10-2012, 07:36 PM

Something like this? Please make a copy and test on your copy.

Sub CompareTwo()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rngCell As Range
Dim rngSource As Range
Dim rngCell2 As Range
Dim rngCompare As Range
Dim lngCalcState As Long

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")
lngCalcState = Application.Calculation

   With Application
      .ScreenUpdating = False
      .EnableEvents = False
      .Calculation = xlCalculationManual
   End With

      With ws1
         Set rngSource = .Range("A2", .Range("A2").End(xlDown))
      End With
   
      With ws2
         Set rngCompare = .Range("A2", .Range("A2").End(xlDown))
      End With
   
      For Each rngCell In rngSource
         For Each rngCell2 In rngCompare
            If rngCell2.Value = rngCell.Value Then
               With rngCell2.Offset(0, 1).Resize(1, 6).Font
                  .Italic = True
               End With
            End If
         Next
      Next

   With Application
      .ScreenUpdating = True
      .EnableEvents = True
      .Calculation = lngCalcState
   End With

End Sub