Create Dynamic Range Formatting with Excel VBA

Scenario:

We will create a dynamic range that automatically adjusts based on data entry and apply conditional formatting to highlight specific cells within the dynamic range. The goal is to format the range dynamically as new data is added or removed.

Step-by-Step Guide:

1. Set Up a Dynamic Named Range: First, we define a dynamic range that will expand or contract based on the data in a specific column. Let’s assume we are working with data in columns A to C, starting from row 1.
2. Conditional Formatting: We’ll also apply conditional formatting to highlight cells that meet certain criteria. For example, we might want to highlight cells in column B that are greater than 100.

VBA Code:

Sub CreateDynamicRangeAndApplyFormatting()
    Dim ws As Worksheet
    Dim dynamicRange As Range
    Dim lastRow As Long
    Dim dataRange As Range   
    ' Set the worksheet object
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Step 1: Identify the last row of data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' Step 2: Define the dynamic range from column A to C based on the last row
    Set dataRange = ws.Range("A1:C" & lastRow)   
    ' Step 3: Clear any existing conditional formatting in the range
    dataRange.FormatConditions.Delete   
    ' Step 4: Apply conditional formatting - Highlight cells in column B > 100
    With dataRange.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="100")
        .Interior.Color = RGB(255, 0, 0) ' Red fill color for values greater than 100
        .Font.Color = RGB(255, 255, 255) ' White font color
    End With   
    ' Step 5: Apply a border to the dynamic range
    With dataRange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = RGB(0, 0, 0)
        .TintAndShade = 0
        .Weight = xlThin
    End With  
    ' Optional Step 6: Create a dynamic named range
    ' This will create a dynamic range called "DynamicRange" in the workbook
    ThisWorkbook.Names.Add Name:="DynamicRange", RefersTo:=dataRange   
    ' Notify the user that the dynamic range and formatting have been applied
    MsgBox "Dynamic range and formatting applied successfully!", vbInformation
End Sub

Explanation:

1. Set the Worksheet Object (ws):
   • We define the worksheet on which the range will be applied. In this example, we are using « Sheet1 ».
2. Find the Last Row of Data:
   • We determine the last row with data in column A using ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row. This ensures that the dynamic range adjusts based on the data length, even if rows are added or removed.
3. Define the Dynamic Range (dataRange):
   • The range is defined from cell A1 to the last row in column C (ws.Range(« A1:C » & lastRow)). This will expand or contract as needed when rows are added or removed.
4. Clear Existing Conditional Formatting:
   • Before applying new conditional formatting, we clear any existing formats in the data range with dataRange.FormatConditions.Delete.
5. Apply Conditional Formatting:
   • We use the .FormatConditions.Add method to apply a rule where any value greater than 100 in column B will be highlighted with a red background and white font. This highlights cells where values exceed a certain threshold.
6. Apply a Border to the Range:
   • A bottom border is applied to the dynamic range using dataRange.Borders(xlEdgeBottom). The border will appear beneath the range, giving it a more structured appearance.
7. Create a Dynamic Named Range (Optional):
   • The line ThisWorkbook.Names.Add Name:= »DynamicRange », RefersTo:=dataRange creates a named range called DynamicRange that refers to the dynamic range. This range will always refer to the current data in columns A to C.
8. Message Box:
   • Once the range and formatting have been applied, a message box notifies the user that the operation was successful.

Conclusion:

This VBA code demonstrates how to create a dynamic range that adjusts as data changes in your worksheet. It also applies conditional formatting to highlight values based on certain criteria and adds borders for better visual organization.