Automate Your Spreadsheets with Conditional Formatting and VBA Macros in Excel

Author: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

Published on: [Date]

The Problem with Conditional Formatting in Excel

Conditional formatting is a powerful feature that allows you to automatically change the appearance of cells based on specific criteria. However, many users struggle when they want to not only format but also alter cell values conditionally.

For example, you might want to change the value in column K to “TBD” if a corresponding cell in column F is greater than $0.00.

The Root Cause of Conditional Formatting Limitations

Excel’s built-in conditional formatting only changes how cells look (e.g., font color, fill color), not their actual values or contents. This limitation can be frustrating when you need to dynamically update cell data based on conditions across your worksheet.

A Step-by-Step Solution: Using Formulas and VBA

Here’s a step-by-step guide that covers both formula-based approaches and using VBA for more advanced scenarios:

Step 1: Understanding the Formula Approach

The simplest way to achieve conditional value changes is by using formulas. However, this method requires you to replace existing data with formulas.

In cell K2, enter:
=IF(F2 > 0, "TBD", "")

This formula checks if the corresponding F column value in row 2 (F2) is greater than $0. If it is, K2 will display “TBD”; otherwise, it remains empty.

Note: This approach overwrites existing data with formulas and may not be suitable for all scenarios where you need to preserve original values or use non-formula cells.

Step 2: Using VBA Macros

A more flexible solution is using a VBA macro that runs automatically when your workbook opens, updating cell values based on conditions. Here’s how:

1. Press Alt + F11 to open the Visual Basic for Applications editor.
2. Insert a new module by right-clicking any existing module or the workbook name in the Project Explorer and selecting "Insert" > "Module".
3. Copy and paste this VBA code into your new module:

Sub UpdateTBDValues()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).row

    Dim i As Long
    For i = 2 To lastRow ' Assuming row 1 is headers
        If ws.Cells(i, 6).Value > 0 Then
            ws.Cells(i, 11).Value = "TBD"
        Else
            ws.Cells(i, 11).Value = ""
        End If
    Next i

End Sub

This macro will loop through all rows in column F and update the corresponding cell in column K to “TBD” if the value is greater than $0. To run this automatically when you open your workbook:

1. In the VBA editor, double-click on 'ThisWorkbook' under Microsoft Excel Objects.
2. Copy and paste this code into the ThisWorkbook module:

Private Sub Workbook_Open()
    Call UpdateTBDValues
End Sub

Now every time you open your workbook, it will automatically update column K based on values in column F.

The Advanced Variation: Using CelTools for Automation

For those who frequently need to automate complex Excel tasks beyond simple conditional formatting and VBA, tools like CelTools can be a game-changer.

CelTools offers over 70 extra Excel features for auditing, formulas, and automation. With CelTools:

• Automate repetitive tasks: Save time by automating complex data updates.
• Advanced conditional formatting options: Go beyond basic rules with custom scripts.
• Error prevention and debugging tools: Easily identify and fix issues in your spreadsheets.

Avoiding Common Mistakes: Best Practices for Conditional Value Updates

• Preserve original data: When using formulas, ensure you’re not overwriting important information. Always back up your workbook before making significant changes.
• Test macros on a sample sheet first: Before applying VBA to critical spreadsheets, test it thoroughly in a duplicate or sample file to avoid unintended data loss.

A Technical Summary: Combining Manual Techniques with Specialized Tools

The combination of manual techniques (formulas and basic VBA) along with specialized tools like CelTools provides the most robust solution for conditional value updates in Excel. While formulas offer a quick, no-code approach suitable for simple tasks, VBA macros provide flexibility and automation for more complex scenarios.

For advanced users or those who frequently need to automate such processes, investing time into learning tools like CelTools can significantly enhance productivity. These tools not only streamline repetitive tasks but also offer additional features that go beyond Excel’s built-in capabilities.

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical