Solved

# Hiding columns Where Conditional Format was not "activated"

Posted on 2014-01-24
216 Views
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
Question by:Patrick O'Dea

LVL 48

Assisted Solution

Rgonzo1971 earned 167 total points
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

LVL 31

Assisted Solution

Rob Henson earned 167 total points
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 Comment

Rgonzo,

ActiveCondition
Sub or function not defined??

See attached.
0

LVL 48

Expert Comment

Hi,

The functions are on the link

Regards
0

Author Comment

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 Comment

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

LVL 80

Accepted Solution

byundt earned 166 total points
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 Closing Comment

Thanks folks for great responses.

I went with the solution provided by byundt!
0

## Featured Post

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …