I have a dropdown menu with entries connected to conditional formatting to change the color. This color coding is used in a specific row of each column in the range. When I change the dropdown selection, the cell changes color. I want to be able to make a new selection from the dropdown and have the columns where that specific row does not match the dropdown color be hidden.

Sub EffectFilter(ByVal Effects As Range)

    ' Dimensionalization
    Dim KeyCells As Range


    ' Initialization
    'Setting the range of the CEM Effects
    Set KeyCells = Range("Q31:BS31")
    
    'Body
    ' Detect Background Color of "Filter Effects" cell, Compare to Effects cells Background colors
    If Not KeyCells.Interior.ColorIndex = Range("G1:H1").Interior.ColorIndex Then
    
        KeyCells.EntireColumn.Hidden = True
        
    End If
    

End Sub

This is my code currently. When I pull up the macros tab, it is not present, so I assume it will execute automatically when the color of Range("G1:H1") changes. Cells G1 and H1 have been merged and centered, but nothing currently happens to the columns when I change the dropdown menu selection. Each of the columns in the Range("Q31:BS31") can be or is a different color, but the colors do repeat. I made sure the colors for the dropdown are the exact same as those present in the columns. What I am hoping to do is to select something from the dropdown and the color of that cell will change (This part works already) and the Columns in which Row 31 is not that color will be hidden. I do plenty of things with VBA where you press a button to run a macro, but this is my first time trying to do something automatically like this, so I may be a bit slow to understand at first, but any help is greatly appreciated.

CodePudding user response：

1. Use the Worksheet_Change event handler (and ensure the code is in the sheet code module, not a regular module).
2. Use a loop over the columns to be hidden.
3. You can think of the value of the dropdown (merged cell) as being stored in the top left corner. So use Range("G1"), not Range("G1:H1").
4. Color or ColorIndex applied by conditional formatting is accessed using .DisplayFormat.Interior.

Private Sub Worksheet_Change(ByVal Target As Range)
   ' Only proceed if the dropdown changed
   If Intersect(Target, Me.Range("G1")) Is Nothing Then Exit Sub

   Dim KeyCells As Range, cell As Range
   Set KeyCells = Me.Range("Q31:BS31")

   ' Unhide all columns by default
   KeyCells.EntireColumn.Hidden = False

   For Each cell in KeyCells
      If cell.DisplayFormat.Interior.ColorIndex <> Me.Range("G1").DisplayFormat.Interior.ColorIndex Then
           cell.EntireColumn.Hidden = True
      End If
   Next
End Sub