Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double
     Dim Sum As Double
     Application.Volatile True
 
     For Each cell In DataRange
         If cell.Interior.Color = ColorSample.Interior.Color Then
             Sum = Sum + cell.Value
         End If
     Next cell
     SumByColor = Sum
 End Function

 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1" ;) ) Is Nothing Then
    Columns("B:H" ;) .EntireColumn.Hidden = False
    If [a1] <> "" Then
    For Each c In Range("B2:H2" ;) 
    If c <> [a1] And c.Offset(0, -1) <> [a1] Then
    Application.ScreenUpdating = False
    c.EntireColumn.Hidden = True
    End If
    Next
    End If
    End If
End Sub