Create Dynamic Range Highlighting with Excel VBA

Objective

The VBA code will:

1. Identify a dynamic range (e.g., a column with data that varies in length).
2. Highlight the range based on specific conditions (e.g., values greater than a threshold).
3. Update the highlighting dynamically when new data is added or removed.

VBA Code

Below is a well-commented VBA script to implement dynamic range highlighting:

Sub HighlightDynamicRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim cell As Range
    Dim highlightColor As Long
    Dim threshold As Double   
    ' Set worksheet (modify to suit your needs)
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Define the column to scan (e.g., Column A)
    Dim col As String
    col = "A"   
    ' Find the last used row in the specified column
    lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row   
    ' Define the dynamic range
    Set rng = ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col)) ' Start from row 2 to ignore headers   
    ' Define highlight color (Yellow)
    highlightColor = RGB(255, 255, 0)   
    ' Set condition threshold (e.g., highlight values greater than 50)
    threshold = 50   
    ' Clear previous formatting
    rng.Interior.ColorIndex = xlNone   
    ' Loop through each cell and apply conditional formatting
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            If cell.Value > threshold Then
                cell.Interior.Color = highlightColor
            End If
        End If
    Next cell   
    ' Cleanup
    Set rng = Nothing
    Set ws = Nothing  
    MsgBox "Highlighting applied successfully!", vbInformation, "Highlight Dynamic Range"
End Sub

Detailed Explanation

1. Setting Up the Worksheet and Variables

• The script starts by defining the worksheet (ws) and the column (col) to be checked.
• lastRow is used to find the last row in the column dynamically.

2. Identifying the Dynamic Range

• The script uses:

• lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row

This finds the last non-empty cell in column « A » (changeable).

• The dynamic range is then set using:

• Set rng = ws.Range(ws.Cells(2, col), ws.Cells(lastRow, col))

This ensures that the range starts from row 2 (excluding headers).

3. Applying Conditional Highlighting

• A loop goes through each cell in the dynamic range.
• If a cell contains a numeric value greater than 50, the cell is highlighted in yellow (RGB(255, 255, 0)).
• Before applying new formatting, the script clears any previous highlighting.

4. Cleaning Up

• The script releases memory by setting objects to Nothing.
• A message box notifies the user upon successful execution.

How to Use

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. Insert a new module (Insert → Module).
3. Copy and paste the code into the module.
4. Run the macro (F5).
5. Modify col, threshold, and highlightColor as needed.