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

[sugibala]

Hi

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

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
        Else
            TimeZone_rng.Interior.Color = vbWhite


        End If
    Next
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.