Solved

Hiding columns Where Conditional Format was not "activated"

Posted on 2014-01-24
8
216 Views
Last Modified: 2014-01-25
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
Comment
Question by:Patrick O'Dea
8 Comments
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 167 total points
Comment Utility
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
 
LVL 31

Assisted Solution

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

by:Patrick O'Dea
Comment Utility
Rgonzo,

ActiveCondition
Sub or function not defined??

See attached.
AdctiveCondition.pdf
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

The functions are on the link

Regards
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Patrick O'Dea
Comment Utility
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
 

Author Comment

by:Patrick O'Dea
Comment Utility
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
 
LVL 80

Accepted Solution

by:
byundt earned 166 total points
Comment Utility
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
 

Author Closing Comment

by:Patrick O'Dea
Comment Utility
Thanks folks for great responses.

I went with the solution provided by byundt!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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 …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now