How to create a drop down list with color no text

Answer

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("A1") 'Change to your dropdown cell
    
    If Not Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False
        Select Case Target.Value
            Case "Red"
                Target.Interior.Color = RGB(255, 0, 0)
                Target.Font.Color = RGB(255, 0, 0)
            Case "Yellow"
                Target.Interior.Color = RGB(255, 255, 0)
                Target.Font.Color = RGB(255, 255, 0)
            Case "Green"
                Target.Interior.Color = RGB(0, 176, 80)
                Target.Font.Color = RGB(0, 176, 80)
            Case ""
                Target.Interior.ColorIndex = xlNone
                Target.Font.ColorIndex = xlAutomatic
        End Select
        Application.EnableEvents = True
    End If
End Sub

Step 5: Save the workbook as macro-enabled

1. Press Alt + Q to close the VBA Editor.
2. Click File, then Save As.
3. Select Excel Macro-Enabled Workbook (.xlsm) from the Save as type dropdown.
4. Click Save.

Step 6: Test the macro

1. Return to your worksheet.
2. Select an option from the dropdown.
3. The cell fills with the corresponding color while hiding the text.

How do you apply the VBA method to multiple cells?

To apply the VBA method to multiple cells, you modify the code to reference a range instead of a single cell, allowing the macro to respond to changes in any cell within the specified dropdown range.

Replace the range reference in the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("A1:A20") 'Adjust range as needed
    
    If Not Intersect(Target, rng) Is Nothing Then
        Application.EnableEvents = False
        Dim cell As Range
        For Each cell In Intersect(Target, rng)
            Select Case cell.Value
                Case "Red"
                    cell.Interior.Color = RGB(255, 0, 0)
                    cell.Font.Color = RGB(255, 0, 0)
                Case "Yellow"
                    cell.Interior.Color = RGB(255, 255, 0)
                    cell.Font.Color = RGB(255, 255, 0)
                Case "Green"
                    cell.Interior.Color = RGB(0, 176, 80)
                    cell.Font.Color = RGB(0, 176, 80)
                Case ""
                    cell.Interior.ColorIndex = xlNone
                    cell.Font.ColorIndex = xlAutomatic
            End Select
        Next cell
        Application.EnableEvents = True
    End If
End Sub

What are the primary use cases for color-only dropdowns?

Status indicators

Color-only dropdowns provide quick visual cues without text clutter for binary or categorical statuses. Green represents "Approved" or "Complete," while red indicates "Rejected" or "Pending." Clinical trial tracking uses this method to flag patient eligibility (green) versus exclusions (red) during quality control reviews.

Priority levels

Red, yellow, and green options indicate urgency levels (High, Medium, Low) in project trackers and risk assessments. This setup minimizes reading effort for team dashboards and aids rapid scanning in large datasets during statistical programming workflows.

Progress tracking

Visual progress markers use color gradients for milestones: blue for "In Progress" and gray for "Not Started." These markers support agile burndown charts and trial enrollment visuals, though status indicators remain the dominant use due to their simplicity.

What are the limitations of color-only dropdowns across Excel versions?

Conditional formatting limitations

Conditional formatting rules created in newer Excel versions may not be compatible with older versions. The workbook is not compatible with the version of Excel you are using. Conditional formatting rules created in newer versions of Excel may not be compatible with older versions of Excel.

Excel's Compatibility Checker identifies formatting-related issues when saving to earlier file formats. Before you continue saving the workbook to an earlier file format, you should address issues that cause a significant loss of functionality so that you can prevent permanent loss of data or incorrect functionality.

Rule order affects behavior when multiple conditions exist. Excel evaluates conditional formatting rules in the order they appear in the list. If two rules conflict, the rule higher up in the list takes priority.

VBA macro limitations

For versions of Excel prior to 2007: the number of colors is limited (the closest available color to the RGB value will be used).

The ColorIndex property provides access to only 56 colors. The ColorIndex property allows access to a limited range of colors, numbered from 1 to 56.

Excessive use of ColorIndex in loops can slow down your macro. To optimize performance, minimize the number of times you write to the ColorIndex property within loops.

File format requirements

Excel Macro-Enabled Workbook (.xlsm) is similar to the default .xlsx format, except macros are allowed.

Workbooks with VBA code must be saved in this format for macros to function.

If your Excel workbook containing macros is saved as a .xlsx file, the macros won't work. Macros only work when the file is saved as a Macro-Enabled Format.

Security settings

Macros are disabled, but security alerts appear if there are macros present. Use this setting to enable macros on a case-by-case basis.

Users must enable macros through Trust Center settings or by clicking "Enable Content" in the security warning bar. Corporate environments may have group policies that restrict macro execution.

Common issues and solutions

1. Colors not applying: Verify that conditional formatting rules reference the correct cell range and that rules do not conflict with existing cell formatting.
2. VBA code not executing: Confirm the file is saved as .xlsm and macros are enabled in Trust Center settings.
3. Unexpected colors: Check for overlapping conditional formatting rules. Check for conflicting cell formatting, such as font color or cell background color, and remove any that conflict with the conditional formatting rules.
4. Macro blocked warnings: Move your workbook to a Trusted Location to avoid blocking of macros.
5. Cell references displaying incorrectly: When you select a range for conditional formatting and trying to create a new rule based on a formula, always pay attention to the row number used in the formula; it must match the row number of the first row in the selected range.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report