Column Formatting

Hi there,

It depends on when you want this formatting to happen. You don't really state. My assumption was you wanted this to take affect every time you deselect the 'Settings' sheet. The below code goes into the worksheet module of 'Settings' (right click sheet tab, select 'View Code', paste). It will apply the formats specified. I think it's what you want. If not, please be specific as to what you need.

Private Sub Worksheet_Deactivate()

    Dim ColumnMatch As Long
    Dim ColumnIndex As Long
    Dim ColumnNames As Variant
    Dim ColumnFormats As Variant

    ColumnNames = Me.Range("D2", Me.Cells(Me.Rows.count, "D").End(xlUp)).Value
    ColumnFormats = Me.Range("E2", Me.Cells(Me.Rows.count, "E").End(xlUp)).Value
    
    For ColumnIndex = LBound(ColumnNames) To WorksheetFunction.Min(UBound(ColumnNames), UBound(ColumnFormats))
        
        ColumnMatch = 0
        On Error Resume Next
        ColumnMatch = WorksheetFunction.Match(ColumnNames(ColumnIndex, 1), Sheets("Import").Range("1:1"), 0)
        If ColumnMatch > 0 Then
            Sheets("Import").Columns(ColumnMatch).NumberFormat = ColumnFormats(ColumnIndex, 1)
        End If
        On Error GoTo 0
        
    Next ColumnIndex
    
End Sub

Select allOpen in new window

HTH

Regards,
Zack Barresse

Frankly Speaking i need to add this task before one Sub going to execute which is Module M4_Formulas - Sub CopyFormulas() in attached WB.
i have one module called M0_Master which execute all Sub one by one ...need to call above procedure before Sub CopyFormula.  

Because of file size increases i had uploaded WB with related sheet for this question.

do i copy above code and past to one more module called M4_ColumnFormatting  ? it will gona work ?

Thanks

Perfect