Automate the formatting of Data presentation in an Excel workbook with VBA

The code will perform several common formatting tasks such as:

1. Adjusting column widths.
2. Making headers bold.
3. Applying borders to cells.
4. Changing background color of cells.
5. Aligning cell text.

Goal:

This code formats data in an Excel sheet based on these criteria.

VBA Code for Automating Formatting:

Sub AutomateFormatting()
    ' Declare variables
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim dataRange As Range   
    ' Reference to the active sheet
    Set ws = ThisWorkbook.ActiveSheet   
    ' Find the last row and column with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column   
    ' Define the data range
    Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn))   
    ' 1. Autofit column widths
    ws.Cells.EntireColumn.AutoFit   
    ' 2. Make headers (row 1) bold
    ws.Rows(1).Font.Bold = True  
    ' 3. Apply borders to the cells
    With dataRange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With   
    With dataRange.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With   
    ' 4. Apply background color to the first row (headers)
    ws.Rows(1).Interior.Color = RGB(221, 235, 247) ' Light blue color   
    ' 5. Align text in cells
    With dataRange
        .HorizontalAlignment = xlCenter ' Center horizontally
        .VerticalAlignment = xlCenter ' Center vertically
    End With   
    ' 6. Apply number format (e.g., monetary format) to columns
    Dim i As Integer
    For i = 1 To lastColumn
        If IsNumeric(ws.Cells(2, i).Value) Then
            ws.Columns(i).NumberFormat = "#,##0.00" ' Monetary format
        End If
    Next i   
    ' 7. Change the color of negative values to red
    Dim cell As Range
    For Each cell In dataRange
        If IsNumeric(cell.Value) And cell.Value < 0 Then
            cell.Font.Color = RGB(255, 0, 0) ' Red for negative values
        End If
    Next cell   
    ' 8. Add a thick outer border around the data range
    With dataRange.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With   
    With dataRange.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With   
    With dataRange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With  
    With dataRange.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
End Sub

Detailed Explanation of the Code:

1. Referencing the Active Sheet:
   • Set ws = ThisWorkbook.ActiveSheet assigns ws as the currently active sheet in the workbook.
2. Finding the Last Row and Column with Data:
   • lastRow and lastColumn are used to find the last row with data in the first column and the last column with data in the first row, respectively.
3. Autofitting Column Widths:
   • ws.Cells.EntireColumn.AutoFit automatically adjusts the width of all columns based on the content in each cell.
4. Making the Header Row Bold (Row 1):
   • ws.Rows(1).Font.Bold = True applies bold formatting to the first row (headers).
5. Applying Borders to the Cells:
   • With dataRange.Borders(xlEdgeBottom) and With dataRange.Borders(xlEdgeRight) apply bottom and right borders to each cell in the dataRange.
6. Changing the Background Color of the First Row:
   • ws.Rows(1).Interior.Color = RGB(221, 235, 247) changes the background color of the header row to a light blue.
7. Aligning Text in Cells:
   • dataRange.HorizontalAlignment = xlCenter and dataRange.VerticalAlignment = xlCenter ensure that text in all cells within dataRange is centered both horizontally and vertically.
8. Applying a Number Format (Monetary Format):
   • The code checks if the cell contains a numeric value and applies a monetary format (#,##0.00).
9. Changing Negative Values to Red:
   • If a cell contains a numeric value that is less than zero, the font color of the cell is changed to red.
10. Adding a Thick Outer Border Around the Data Range:
    • The code adds thick borders around the entire data range using xlEdgeTop, xlEdgeLeft, xlEdgeBottom, and xlEdgeRight.

Conclusion:

This code automates several common formatting tasks in Excel. You can customize this code further based on your specific needs, such as modifying colors, number formats, or applying conditions to the formatting. To use the code, just insert it into a VBA module in your Excel workbook, and run it.