Set Excel Table Flags for Red Conditional Formatting with VBA
Question details
Set a flag for each Excel table row when a cell in specified columns displays a red fill.

- 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.
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.
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.
In Excel, open the workbook, press Alt+F11, and choose Insert > Module in the VBA editor.
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
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.
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.

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.

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.




