Format Cells Based on Value With Excel VBA

Overview of the Problem

In Excel, you may want to apply different formatting to cells based on their values, for instance, changing the cell’s color depending on the number within it. Using Excel VBA (Visual Basic for Applications), we can automate this process of conditional formatting to enhance data visualization and improve readability.

This guide will walk you through creating a VBA script that formats cells based on their values. Specifically, we will create a code that:

1. Identifies the range of cells where formatting should be applied.
2. Checks the value in each cell.
3. Changes the cell’s formatting (such as background color, font color, etc.) based on its value.

Example: Formatting Cells Based on Their Values

Step 1: Define the Range

We begin by defining the range of cells in which we want to apply the formatting. You can use a specific range (e.g., A1:A10) or a dynamic range based on your worksheet’s data.

Step 2: Set Conditions for Formatting

For each cell in the defined range, we’ll check its value and apply a specific formatting based on the value.

• Example Condition 1: If the cell’s value is greater than 50, change the background color to green.
• Example Condition 2: If the cell’s value is less than 50 but greater than 20, change the background color to yellow.
• Example Condition 3: If the cell’s value is less than 20, change the background color to red.

Step 3: Write the VBA Code

Now, let’s look at the detailed VBA code to achieve this.

Sub FormatCellsBasedOnValue()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    ' Set the worksheet where the formatting will be applied
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
    ' Define the range to apply formatting to (e.g., A1:A10)
    Set rng = ws.Range("A1:A10") ' Change this range as needed
    ' Loop through each cell in the range
    For Each cell In rng
        If IsNumeric(cell.Value) Then ' Ensure the cell contains a number
            ' Check if the cell's value is greater than 50
            If cell.Value > 50 Then
               cell.Interior.Color = RGB(0, 255, 0) ' Green background
                cell.Font.Color = RGB(255, 255, 255) ' White font
            ' Check if the cell's value is between 20 and 50
            ElseIf cell.Value > 20 Then
                cell.Interior.Color = RGB(255, 255, 0) ' Yellow background
                cell.Font.Color = RGB(0, 0, 0) ' Black font
            ' Check if the cell's value is less than or equal to 20
            Else
                cell.Interior.Color = RGB(255, 0, 0) ' Red background
                cell.Font.Color = RGB(255, 255, 255) ' White font
            End If
        Else
            ' If the cell does not contain a number, reset formatting
            cell.Interior.ColorIndex = xlNone ' No background color
            cell.Font.ColorIndex = xlNone ' No font color change
        End If
    Next cell
End Sub

Detailed Explanation of the Code

1. Set the Worksheet and Range

Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
Set rng = ws.Range("A1:A10") ' Change this range as needed

• ThisWorkbook.Sheets(« Sheet1 »): This line specifies the worksheet where you want the formatting to be applied. In this example, it’s « Sheet1 ». You can change « Sheet1 » to the name of the sheet you’re working with.
• ws.Range(« A1:A10 »): This specifies the range of cells where you want the formatting to be applied. Here, it’s the range from A1 to A10, but you can modify this to apply to any range of cells.

2. Looping Through the Cells

For Each cell In rng

• For Each cell In rng: This starts a loop that goes through each individual cell in the range rng (i.e., from A1 to A10 in this case).

3. Check if the Cell Contains a Number

If IsNumeric(cell.Value) Then

• If IsNumeric(cell.Value) Then: This condition checks if the cell contains a numeric value. If it does, the code continues to apply the conditional formatting. If the cell contains a non-numeric value, the formatting will be reset.

4. Apply Conditional Formatting Based on Value

Here are the conditions we check for and apply formatting:

• If the cell’s value is greater than 50:
• If cell.Value > 50 Then
•     cell.Interior.Color = RGB(0, 255, 0) ‘ Green background
•     cell.Font.Color = RGB(255, 255, 255) ‘ White font
• If the cell’s value is between 20 and 50:
• ElseIf cell.Value > 20 Then
•     cell.Interior.Color = RGB(255, 255, 0) ‘ Yellow background
•     cell.Font.Color = RGB(0, 0, 0) ‘ Black font
• If the cell’s value is less than or equal to 20:
• Else
•     cell.Interior.Color = RGB(255, 0, 0) ‘ Red background
•     cell.Font.Color = RGB(255, 255, 255) ‘ White font
• The Interior.Color property is used to change the cell’s background color. The RGB function takes three parameters representing the Red, Green, and Blue color values.
• The Font.Color property changes the text color in the cell.

5. Reset Formatting for Non-Numeric Cells

Else
    cell.Interior.ColorIndex = xlNone ' No background color
    cell.Font.ColorIndex = xlNone ' No font color change
End If

If the cell doesn’t contain a numeric value (for example, text), the formatting is reset to its default state, and no background or font color changes are made.

6. End the Loop

Next cell

This marks the end of the For Each loop. After this line, the code will continue checking the next cell in the defined range.

How to Use the Code

1. Open the VBA Editor: Press Alt + F11 in Excel to open the VBA editor.
2. Insert a Module: In the VBA editor, click on Insert > Module to add a new module.
3. Copy and Paste the Code: Copy the code above and paste it into the module.
4. Run the Macro: Press F5 or go to Run > Run Sub/UserForm to execute the macro.

Conclusion

This code allows you to apply custom formatting based on the values in a specified range of cells in Excel. You can adjust the ranges and conditions as needed to fit your specific use case. By automating the process with VBA, you can quickly highlight important data, which improves both the appearance and usability of your worksheets.