VBA & Macro Problems

Set Excel Table Flags for Red Conditional Formatting with VBA

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

Set a flag for each Excel table row when a cell in specified columns displays a red fill.

Set Excel Table Flags for Red Conditional Formatting with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Use VBA and table column names to check conditional formatting in a table that can grow or shrink.
Observed behavior
Each row should receive a flag of 1 if any specified cell displays red, or 0 otherwise.
Before you start

Identify the worksheet, table, columns to check, flag column, and exact red fill color used by the conditional formatting rule. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and ensure the flag cells are writable.

Solution 1Recommended

Use a VBA macro to check displayed colors by table column

Use this approach to check the table's current rows and set a numeric flag based on the displayed fill color.

DisplayFormat.Interior.Color reads the displayed fill, including conditional formatting. The example checks for exactly RGB(255, 0, 0); change that value if your rule uses a different shade of red.

The macro checks every current table row when it runs. It does not automatically run when the table changes.

1
Open the VBA editor

In Excel, open the workbook, press Alt+F11, and choose Insert > Module in the VBA editor.

2
Add the flagging macro

Paste this code into the module: Sub SetRedFlags() Dim ws As Worksheet Dim tbl As ListObject Dim tableRow As ListRow Dim columnName As Variant Dim columnsToCheck As Variant Dim flagIndex As Long Dim hasRed As Boolean Dim previousEvents As Boolean Dim errorNumber As Long Dim errorMessage As String previousEvents = Application.EnableEvents On Error GoTo CleanUp Set ws = ThisWorkbook.Worksheets("Sheet1") Set tbl = ws.ListObjects("Table1") columnsToCheck = Array("Check A", "Check B") flagIndex = tbl.ListColumns("Flag").Index If tbl.DataBodyRange Is Nothing Then GoTo CleanUp Application.EnableEvents = False For Each tableRow In tbl.ListRows hasRed = False For Each columnName In columnsToCheck If tableRow.Range.Cells(1, tbl.ListColumns(CStr(columnName)).Index).DisplayFormat.Interior.Color = RGB(255, 0, 0) Then hasRed = True Exit For End If Next columnName If hasRed Then tableRow.Range.Cells(1, flagIndex).Value2 = 1 Else tableRow.Range.Cells(1, flagIndex).Value2 = 0 End If Next tableRow CleanUp: errorNumber = Err.Number errorMessage = Err.Description On Error Resume Next Application.EnableEvents = previousEvents On Error GoTo 0 If errorNumber <> 0 Then MsgBox "Flag update failed: " & errorMessage, vbExclamation End Sub

3
Replace the example names

In the code, replace Sheet1, Table1, Check A, Check B, and Flag with your worksheet, table, checked column, and flag column names. Add or remove names in Array("Check A", "Check B") as needed.

4
Run and check the results

Return to Excel, recalculate the workbook if needed, then select Developer > Macros, choose SetRedFlags, and select Run. Check that red rows show 1 and other rows show 0; run the macro again after changing the table.

Use a VBA macro to check displayed colors by table column
Protected sheets: If protection prevents the macro from writing to the flag column, unprotect the sheet before running it or make the flag cells writable. Reapply protection afterward if needed.
Free Microsoft Office alternative

Work with Excel tables in WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday workbook tasks to WPS. Excel-specific VBA and DisplayFormat behavior may not be compatible, so test macros before relying on them.

Open and edit spreadsheets in a familiar table-based interface.Work with common Microsoft Excel formats, including .xlsx.Review table data and formatting without a Microsoft Office subscription.Use a lightweight office suite for everyday document and spreadsheet tasks.
QA img-9

Frequently Asked Questions

Why use DisplayFormat instead of Interior.Color?

DisplayFormat.Interior.Color checks the fill displayed after conditional formatting is applied. Interior.Color alone reads the cell's underlying fill and may miss a color produced by a rule.

Will the macro include rows added to the table?

Yes. The macro loops through the current ListRows in Table1 each time you run it, so newly added rows are included. Run it again after adding or removing rows.

Why does a red-looking cell get a flag of 0?

The example matches only RGB(255, 0, 0). Check the fill color in Home > Conditional Formatting > Manage Rules and change the RGB value in the macro to match the rule's exact color.

Can I use this macro as a worksheet formula?

No. Run it as a VBA macro through Developer > Macros; DisplayFormat is not available from a user-defined function called by a worksheet formula.

Does the macro run automatically when formatting changes?

No. The example runs only when you invoke it. An event-based macro can trigger updates for selected changes, but formatting can also change after recalculation, so the trigger must be chosen for the workbook.