Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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
Asked:
Patrick O'Dea
3 Solutions
 
Rgonzo1971Commented:
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

Open in new window

Regards
0
 
Rob HensonIT & Database AssistantCommented:
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
 
Patrick O'DeaAuthor Commented:
Rgonzo,

ActiveCondition
Sub or function not defined??

See attached.
AdctiveCondition.pdf
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rgonzo1971Commented:
Hi,

The functions are on the link

Regards
0
 
Patrick O'DeaAuthor 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??

Thanks for your persistence!
0
 
Patrick O'DeaAuthor 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??

Thanks for your persistence!
HideCondits.xlsm
0
 
byundtCommented:
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

Open in new window

0
 
Patrick O'DeaAuthor Commented:
Thanks folks for great responses.

I went with the solution provided by byundt!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now