Applying VBA Subs Across Multiple Worksheets

VBA, or Visual Basic for Applications, is a programming language used within Excel to automate tasks. A subroutine, or “sub,” is a code block that performs a specific task. When you apply a sub to worksheets, you’re instructing Excel to execute certain operations on those sheets.

Procedures

To apply a sub to multiple worksheets, you’ll need to:

1. Open the Visual Basic for Applications editor by pressing Alt + F11.
2. Insert a new module by right-clicking on any existing one and selecting Insert > Module.
3. Write your sub within the module.

Here’s a basic sub that changes the background color of each cell in a worksheet:

Sub ChangeBackgroundColor()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.Interior.Color = RGB(255, 255, 0) ' Yellow color
    Next ws
End Sub

Imagine you have a workbook with monthly sales data for different regions and you want to highlight sales above $10,000 in yellow.

Data Example

VBA Subroutine

Sub HighlightHighSales()
    Dim ws As Worksheet
    Dim cell As Range
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.Range("B2:D5")
            If cell.Value > 10000 Then
                cell.Interior.Color = RGB(255, 255, 0) ' Yellow color
            End If
        Next cell
    Next ws
End Sub

Result

After running the HighlightHighSales sub, any cell with a value greater than $10,000 will be highlighted in yellow.

This example demonstrates how to apply a VBA sub to multiple worksheets to perform calculations and format cells based on specific criteria. Remember to always test your VBA code on a copy of your data to prevent any unintended changes to your worksheets.