Skip to content
Advertisement

Vertical Filter in MS-Excel by color

Is there a way to (not hide) but filter, columns in excel instead of rows.

enter image description here

This is a sample of data, it continues further. I need to be able to say filter only the red columns – display only the red columns (and possibly green) to show me which data needs fixing in the code and what the data is.

Hiding and un-hiding and transposing takes time and effort and there is far too much work that i don’t want to spend time doing that. I need to be able to open it up and close the filter quickly to confirm how the other values are being updated.

I’m not against using VBA or code, but i need this to transition between the two states quickly.

Thank you.

Advertisement

Answer

If your columns are colored in a way that does not change, then explore custom views.

This may not do what you want, but will hide every column where the color doesn’t match the active cell. Run again would unhide everything. The range of columns could be adjusted to something less inclusive if you like

Dim rng As Range
Dim color As String
color = ActiveCell.Interior.color
 For Each rng In Range("A:IV").Columns
  If rng.Interior.color <> color And rng.EntireColumn.Hidden = False Then rng.EntireColumn.Hidden = True Else rng.EntireColumn.Hidden = False
 Next rng
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement