Automate Data formatting in Excel VBA

This code includes common formatting tasks that you can customize to suit your needs, such as applying font styles, changing colors, managing borders, adjusting column widths, aligning data, and formatting numbers.

Code Objectives:

1. Apply font formatting to a data range.
2. Apply background colors to headers.
3. Auto-adjust column widths based on content.
4. Align text in cells.
5. Apply borders to the data range.
6. Format numbers (e.g., currency or numeric).

VBA Code:

Sub AutomateFormatting()
    ' Declare variables
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim headerRange As Range   
    ' Reference the active sheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace with your actual sheet name   
    ' Define the data range (e.g., A1 to D10)
    Set dataRange = ws.Range("A1:D10") ' Replace with your actual data range   
    ' Define the header range (e.g., A1 to D1)
    Set headerRange = ws.Range("A1:D1") ' Replace with your header range   
    ' --- Font Formatting ---
    With dataRange
        .Font.Name = "Calibri" ' Font name
        .Font.Size = 11 ' Font size
        .Font.Color = RGB(0, 0, 0) ' Font color (black)
    End With   
    ' --- Header Formatting ---
    With headerRange
        .Font.Bold = True ' Bold font
        .Font.Size = 12 ' Font size for headers
        .Interior.Color = RGB(0, 112, 192) ' Blue background color
        .Font.Color = RGB(255, 255, 255) ' Font color (white)
        .HorizontalAlignment = xlCenter ' Center horizontally
    End With  
    ' --- Auto-fit Columns ---
    ws.Columns.AutoFit ' Auto-adjust column widths based on content   
    ' --- Text Alignment ---
    With dataRange
        .HorizontalAlignment = xlCenter ' Center horizontally
        .VerticalAlignment = xlCenter ' Center vertically
    End With   
    ' --- Apply Borders ---
    With dataRange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = RGB(0, 0, 0) ' Border color (black)
        .TintAndShade = 0
        .Weight = xlThin ' Border weight (thin)
    End With   
    With dataRange.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = RGB(0, 0, 0) ' Border color (black)
        .TintAndShade = 0
        .Weight = xlThin ' Border weight (thin)
    End With   
    ' --- Number Formatting ---
    ' Apply currency format to column B (e.g., B2:B10)
    ws.Range("B2:B10").NumberFormat = "#,##0.00 €" ' Currency format with two decimals   
    ' --- Apply Conditional Formatting ---
    ' Example: Change background color for cells greater than 1000 in column C
    With ws.Range("C2:C10").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="1000")
        .Interior.Color = RGB(255, 0, 0) ' Red background
        .Font.Color = RGB(255, 255, 255) ' White font
    End With
    MsgBox "Formatting has been applied successfully!", vbInformation
End Sub

Detailed Explanation

1. Variable Declaration:
   • ws: References the active worksheet (in this case, « Sheet1 »).
   • dataRange: The range of data you want to format (e.g., from A1 to D10).
   • headerRange: The header row range (e.g., A1:D1).
2. Font Formatting:
   • The font is set to Calibri, with a size of 11 and the color set to black.
3. Header Formatting:
   • The header text is set to bold, with a font size of 12.
   • The background color is set to blue (RGB(0, 112, 192)), and the font color is set to white (RGB(255, 255, 255)).
   • The text is centered horizontally using xlCenter.
4. Auto-adjust Column Width:
   • The AutoFit method automatically adjusts the width of each column based on the content.
5. Text Alignment:
   • The data in the specified range is aligned centrally both horizontally and vertically.
6. Apply Borders:
   • Borders are applied to the bottom and right edges of each cell in the data range.
   • The border color is black, and the border weight is set to thin.
7. Number Formatting:
   • The values in column B (range B2:B10) are formatted as currency with two decimal places (e.g., 1,000.00 €).
8. Conditional Formatting:
   • A conditional formatting rule is applied to column C (range C2:C10). If a cell value is greater than 1000, the background will turn red and the font will be white.
9. Confirmation Message:
   • After the code executes, a message box informs the user that the formatting has been successfully applied.

Customization

• You can easily adapt this code by modifying the ranges (e.g., A1:D10), the sheet name, colors, fonts, and adding additional formatting features as needed.

How to Run the VBA Code:

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Go to Insert > Module to add a new module.
3. Copy and paste the code into the module.
4. Press F5 or go to Run > Run Sub/UserForm to execute the code.

This will automatically format your data according to the specified rules on your worksheet.