checking a cell range and returning the column number

[jeskit]

Hi

I have a loop macro which currently opens a document, loops through a cell range on the "PPIIIFORM" sheet and first checks to see if the cell is empty, if its not then it returns the cell value into the column A in the Input_Reference_Table sheet. It then checks the cell colour and if its gray it returns false in column L column and if its yellow returns true in the N column on the Input_Reference_Table sheet.

Sub PP3InputRef()

Dim StrFldr As String
Dim PPWB As Workbook
Dim Nrow As Long
Dim cell As Range

Application.DisplayAlerts = False

StrFldr = ThisWorkbook.Path

Set PPWB = Workbooks.Open(StrFldr & "\" & "HDE_PPIII_Input_Reference_Table_V1.xlsx")
PPWB.Sheets.Add.Name = ("Input_Reference_Table"): PPWB.Sheets.Add.Name = ("Values")

PPWB.Sheets("InputRefapd").Range("A1:Y1").Copy Destination:=PPWB.Sheets("Input_Reference_Table").Range("A1:Y1")
 
Nrow = 2

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
    If cell.Value <> "" Then Sheets("Input_Reference_Table").Cells(Nrow, 1).Value = cell.Value
    Sheets("Input_Reference_Table").Cells(Nrow, 12).Value = Not cell.Interior.Color = RGB(217, 217, 217)
    Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = cell.Interior.Color = RGB(255, 255, 0)
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell

However i need to add a line into the code above so that it does another check. It checks the column number of that cell and then returns the column number in the column K in the Input_Reference_Table sheet. However the number needs to start from 1 - 16 so that in the cell range F4:U644 if its F return 1, if its G it returns 2.....if its U it returns 16 etc.

However i am not sure how to do this. Can anyone help me?

Thank you

Jeskit