x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 273

# Hiding columns Where Conditional Format was not "activated"

Hi,

I have tried to resolve this but have ended up with "ugly" solutions.

Simple enough to explain.
See attached.

I have a cells in a sheet which will potentially be "coloured" depending on certain conditional formatting.

I want a solution (probably a macro) which simply "hides" any columns where the formatting is not activated.  See my example attached.  If one cell is coloured then the whole column should remain visible - otherwise hide the column

Ideally, (but not mandatory) the solution should not need to examine the actual format rules.  Ideally, the solution would simply KNOW that a cell has been formatted and hence remains visible.
TrafficLightsV1Solved.xlsm
0
Patrick O'Dea
3 Solutions

Commented:
HI,

you could use Pearson's solution
http://www.cpearson.com/excel/CFColors.htm

Function ActiveCondtion and Function GetStrippedValue used in ActiveCondition

``````Sub Macro5()
For Each cl In Range("B2:E5").Columns
If ActiveCondition(Range(Cells(2, cl.Column), Cells(2, cl.Column).End(xlDown))) Then
cl.EntireColumn.Hidden = True
Else
cl.EntireColumn.Hidden = False
End If
Next
End Sub
``````
Regards
0

Finance AnalystCommented:
If you add a couple of rows at the top of the sheet and add these two array formulas, it will count the number of cells that meet the same criteria as the formatting:

Assumed inserted two rows so data now starts at row 3 and formula in row 1
Red =COUNTIF(B4:B15,"<"&TrafficLights!\$B2:\$B13)
Orange =COUNTIFS(B4:B15,"<"&TrafficLights!\$C2:\$C13,B4:B15,"=>"&TrafficLights!\$B2:\$B13)

Confirm these with Ctrl + Shift + Enter and { } brackets will appear at either end of the formula.

You / We can then setup a routine that hides those columns where both are zero.

Thanks
Rob H
0

Author Commented:
Rgonzo,

ActiveCondition
Sub or function not defined??

See attached.
0

Commented:
Hi,

The functions are on the link

Regards
0

Author Commented:
Hi ,

I have loaded Pearsons stuff and it is still not 100%
It deletes 4 of my rows when only 2 have formatting applied.
See attached.

Perhaps I have missed something??

0

Author Commented:
Hi ,

I have loaded Pearsons stuff and it is still not 100%

See macro5.
It deletes 4 of my rows when only 2 have formatting applied.
See attached.

Perhaps I have missed something??

HideCondits.xlsm
0

Commented:
If you have Excel 2010 or later, you can use the DisplayFormat property of a cell to get the fill color as displayed. This lets you use a very short macro, without need for any of Mr. Pearson's code.
``````Sub HiddenColors()
Dim cel As Range, col As Range, rg As Range
Dim bColor As Boolean
Application.ScreenUpdating = False
Set rg = ActiveSheet.UsedRange
Set rg = rg.Offset(0, 1).Resize(rg.Rows.Count, rg.Columns.Count - 1) 'All the data except column A
For Each col In rg.Columns
bColor = False
For Each cel In col.Cells
If cel.DisplayFormat.Interior.ColorIndex <> -4142 Then
bColor = True
Exit For
End If
Next
col.EntireColumn.Hidden = Not bColor
Next
End Sub
``````
0

Author Commented:
Thanks folks for great responses.

I went with the solution provided by byundt!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.