Get column header from 1 sheet & compare rows with another sheet


New Member
Sep 6, 2021
Office Version
  1. 365
  1. Windows

can any one help me in getting the below scenario.

i have 2 sheets in excel. sheet 1 with data. sheet2 has column headers as reference in column B2
i want to take column headers from sheet1 . Iterate through sheet2 (column 2) .If my header value matches then take the B3 value from sheet 2.It can b M(Mandatory field check), B(Boolean value check) and so on..
I have the code snippet for doing the check operations but it is hardcoded with the columnname which i want to be dynamic (retrieve the column cell based on matching column header) .

And i also struggle to get the header and find it in sheet2. can any of u help. I am attaching the 2 excel sheet and code
VBA Code:
Dim intResult As Integer
Dim Data_sh1 As Worksheet, User_Details_sh2 As Worksheet
Dim Data_Lr As Long, User_Details_lr As Long, lc As Long, lc1 As Long, i As Long, j As Long
Dim a() As Variant, b() As Variant

Set Data_sh1 = Sheets("Service User") 'origin

Set User_Details_sh2 = Sheets("User Details")  'destination

'last row on origin sheet

Data_Lr = Data_sh1.Range("A" & Rows.Count).End(xlUp).Row

'last row on destination sheet

User_Details_lr = User_Details_sh2.Range("A" & Rows.Count).End(xlUp).Row + 1

'Store headers in the "a" variable of the origin sheet

lc = Data_sh1.Cells(1, Columns.Count).End(xlToLeft).Column

a = WorksheetFunction.Transpose(Data_sh1.Range("A1", Data_sh1.Cells(1, lc)).Value)
 'Store headers in the "b" variable of the destination sheet

lc1 = User_Details_sh2.Range("B" & Rows.Count).End(xlUp).Row

b = WorksheetFunction.Transpose(User_Details_sh2.Range("B2", User_Details_sh2.Cells(1, lc1)).Value)
 MsgBox lc
Dim TimeZone_i As Integer
Dim TimeZone_rng As Range
 Dim Picklist_TimeZone As Long

 With Data_sh1
    For TimeZone_i = 1 To .Range("A1").SpecialCells(xlCellTypeLastCell).Column
        Set TimeZone_rng = .Range("A1:A" & TimeZone_i)
        If WorksheetFunction.CountIf(User_Details_sh2.Range("B:B"), TimeZone_rng) = 0 Then 'picklist reference sheet column E
            MsgBox User_Details_sh2.Range("B:B").Cells.Value
            Picklist_TimeZone = Picklist_TimeZone + 1
            TimeZone_rng.Interior.Color = vbWhite

        End If
End With

individual mandatory check code hardcoded with column cell .which i want dynamic too
''Get the last row of a sheet
'LR = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
'Dim External_ID_i As Long
'Dim External_ID_c As Long
'Dim External_ID_myRange As Range
'Dim External_ID_myCell As Range
'Dim MyList(2) As String ' create array with definite size and value
'MyList(0) = "Open" ' assign value
'MyList(1) = "Closed"
'Set External_ID_myRange = Range("A1:A" & LR)
'For Each External_ID_myCell In External_ID_myRange '
'     External_ID_c = External_ID_c + 1
'    If IsEmpty(External_ID_myCell) Then
'        External_ID_myCell.Interior.Color = RGB(255, 87, 87)
'        External_ID_i = External_ID_i + 1
'    Else
'        External_ID_myCell.Interior.Color = vbWhite
'    End If
'Next External_ID_myCell
'If External_ID_i > 0 Then
'MsgBox _
'"External ID (Column A) : There are total " & External_ID_i & " empty cells out of " & External_ID_c & ".Input a Number."

in the attached sheets, sheet1 finds the header in sheet2 of any order, then it takes the value M/B. in my sheet1, lastname row5 s empty which has to be highlighted since its mandate.the same way for country.


  • generic_sheet1.png
    182 KB · Views: 11
  • generic_sheet2.png
    182 KB · Views: 11

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Forum statistics

Latest member

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
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 "".
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