Apply [Numberformat] to Column based on specific [Title/Header] and run across multiple sheets

[Manerlao]

Hi All,

I hope everyone is keeping well.
I need some help with this problem which I have been trying to resolve with no luck...

I am building a Macro to format the specific column based on the column header and run this across multiple sheets in the workbook.
The Macro will be sitting in my Personal Macro Workbook, so it needs to activate on the ActiveWorkbook which I open.

I basically have 7 types of number formatted columns which are:
Date
Text
Time
Number 0dp
Number 2dp
Number 3dp
%

Instead of writing out all the column headers in the VBA code for which the number format should apply to, can I just store all the headers in my Personal Macro Workbook in "Sheet1" and store the headers under the numberformat type.
So in other words, store all Date column numberformats in range A2:A100 within personal macro workbook
Store all Text column numberformats in range B2:B100....
....

Here is my code so far:

Sub FullNmbrFrm()

Dim ws As Worksheet
Dim Cell As Range, rngX As Range
Dim strFormat As String

For Each ws In ActiveWorkbook.Sheets

    Set rngX = Range("A1:DZ1")
    
    '****************************
    'DATE YYYY
    strDATEFormat = "YYYY)"
    'TEXT
    strTXTFormat = "@_)"
    'TIME
    strTIMEFormat = "hh:mm_)"
    'NUMBER 0DP Whole Number
    strNUM1Format = "0_)"
    'NUMBER 2DP 0.0
    strNUM2Format = "0.00_)"
    'NUMBER 3DP 0.0
    strNUM3Format = "0.000_)"
    '%
    strPERCFormat = "%_)"
    '****************************
    
    
    For Each Cell In rngX
    With Cell
    
    Select Case True
    
    Case .Value Like "Date1" Or .Value Like "Date2" Or .value like "(................................................)"
    .EntireColumn.NumberFormat = strDATEFormat
    
    Case .Value Like "Text1" Or .Value Like "Text2" Or .value like "(................................................)"
    .EntireColumn.NumberFormat = strPerFormat
    
    Case .Value Like "Time1" Or .Value Like "Time2" Or .value like "(................................................)"
    .EntireColumn.NumberFormat = strPerFormat
    
    Case .Value Like "AccountA" Or .Value Like "AccountB" Or .value like "(................................................)"
    .EntireColumn.NumberFormat = strNUM1Format
    
    Case .Value Like "Account1A" Or .Value Like "Account2B" Or .value like "(................................................)"
    .EntireColumn.NumberFormat = strNUM2Format
    
    Case .Value Like "Account2A" Or .Value Like "Account2B" Or .value like "(................................................)"
    .EntireColumn.NumberFormat = strNUM3Format
    
    Case .Value Like "Percentage1" Or .Value Like "Percentage2" Or .value like "(................................................)"
    .EntireColumn.NumberFormat = strPERCFormat
    
    End Select
    End With
    Next Cell

Next ws


End Sub

Please let me know if you need any further information to help me with this problem. Thank you very much Excel community!

Kind regards,
Manerlao

 

[Yongle]

Your macro can be simplified like this

Sub FullNmbrFrm()
    Dim ws As Worksheet, Cel As Range
    For Each ws In ActiveWorkbook.Sheets
        For Each Cel In ws.Range("A1", ws.Cells(1, ws.Columns.Count).End(xlToLeft))
            With Cel.EntireColumn
                Select Case Cel.Value
                    Case "Date1", "Date2","Date3","Date4"
                        .NumberFormat = "YYYY"
                    Case "Text1", "Text2","Any Text you want"
                        .NumberFormat = "@_)"
                    Case "Time1", "Time2"
                        .NumberFormat = "hh:mm_)"
                    Case "AccountA", "AccountB"
                        .NumberFormat = "0_)"
                    Case "Account1A", "Account2B"
                        .NumberFormat = "0.00_)"
                    Case "Account2A", "Account2B"
                        .NumberFormat = "0.000_)"
                    Case "Percentage1", "Percentage2"
                        .NumberFormat = "%_)"
                End Select
            End With
        Next Cel
    Next ws
End Sub

For wildcards wildcards, then ...

    With Cel
    Select Case True
        Case Cel.Value Like "Time*"
                      .NumberFormat = "hh:mm_)"
    End Select
    End With

 

[Manerlao]

Your macro can be simplified like this

Sub FullNmbrFrm()
    Dim ws As Worksheet, Cel As Range
    For Each ws In ActiveWorkbook.Sheets
        For Each Cel In ws.Range("A1", ws.Cells(1, ws.Columns.Count).End(xlToLeft))
            With Cel.EntireColumn
                Select Case Cel.Value
                    Case "Date1", "Date2","Date3","Date4"
                        .NumberFormat = "YYYY"
                    Case "Text1", "Text2","Any Text you want"
                        .NumberFormat = "@_)"
                    Case "Time1", "Time2"
                        .NumberFormat = "hh:mm_)"
                    Case "AccountA", "AccountB"
                        .NumberFormat = "0_)"
                    Case "Account1A", "Account2B"
                        .NumberFormat = "0.00_)"
                    Case "Account2A", "Account2B"
                        .NumberFormat = "0.000_)"
                    Case "Percentage1", "Percentage2"
                        .NumberFormat = "%_)"
                End Select
            End With
        Next Cel
    Next ws
End Sub

For wildcards wildcards, then ...

    With Cel
    Select Case True
        Case Cel.Value Like "Time*"
                      .NumberFormat = "hh:mm_)"
    End Select
    End With

That's superb! Thank you very much, Yongle.

Best regards,
Manerlao

 

[Yongle]

thanks for your feedback