Preserving Conditional Formatting with an Advanced Filter

[excelnewb02]

I have a workbook that tracks 72 entries (each in its own row) through a complicated submission and approval process. I've set up a macro to run an Advanced Filter that transfers entries to a separate worksheet (called Risks) based on a number of criteria. So for instance, if an entry has a submission due within one week, it shows up on my "Risks" worksheet. Or if our approval of their submission is due within 5 days, that will also put it onto the "Risks" tab. The macro and filter work well, and I have the macro set to run each time the file opens, and each time the data in the complete worksheet changes. The last thing I'd like to add is some conditional formatting that highlights which column met the filter criteria. My problem is, when I add this formatting to my original list, it disappears on the "Risks" worksheet when I run the macro.

I've tried recording a more in depth macro that re-applies the formatting to the filtered data, but since I don't know how many entries will be filtered each time, I have to apply the formatting down through row 73, and then I end up with highlighted blank columns below the end of the list. Not the end of the world, but doesn't look clean and I'd like to figure out how to only format the list itself. Let me know if I should repost in the "macro" section or if there's an easier way to preserve the formatting. Thanks for any suggestions!

[Richard Buttrey]

Hi,

It seems to me there are two options.

1. Create a dynamic range name that will automatically adjust to cover however many entries are filtered. Then use that range name in the macro which re-applies the conditional formats.

2. Create a variable which will identify the last row in the filtered range and use the variable in the code that works out the range to which the CF should be applied.

HTH

[excelnewb02]

Those both sound great! I'll start Googling to figure out how to do it. Just curious though: in your opinion, would one be preferable over the other?

[excelnewb02]

Working my way through the first option, creating a dynamic named range. And I should warn upfront that my VBA is practically non-existent. I created the dynamic range name using the following in the "Refers To" box:

=OFFSET(Risks!$A$9,0,0,COUNTA(Risks!$A:$A),26)

That seems to work, but I'm having trouble using this range effectively in the macro to reapply the conditional formatting. For example, if I start the macro by selecting this range, how do I then apply conditional within that range to a certain column? Thanks again.

[Richard Buttrey]

Hi,

Create a dynamic range name that is just one column wide and use that name. So assuming your filtered list starts in A9, change the last number from 26 to 1.

Remember that you don't need to select the range you can just address the range directly. e.g.

your_range_name.FormatConditions.Add Type:=xlExpression, Formula1:="your_CF_formuale"

Regards

[excelnewb02]

Thanks so much for your help so far. When I insert this line of code into the macro:


your_range_name.FormatConditions.Add Type:=xlExpression, Formula1:="your_CF_formula"

with the substitutions it looks like this:

Recent5.FormatConditions.Add Type:=xlExpression, Formula1:="=""missing"""

The complete line of code looks like this:

Recent5.FormatConditions.Add Type:=xlExpression, Formula1:="=""missing"""
Recent5.FormatConditions(Recent5.FormatConditions.Count).SetFirstPriority
With Recent5.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Recent5.FormatConditions(1).StopIfTrue = False

When I run that, I get a Runtime 424 Object Required Error on the first line. Probably something obvious, but my VBA is so crude I just have to record macros and then copy and paste different bits of code together making guesses about what goes where. For example, in the above when I recorded the macro, all the "Recent5" instances above where "Select" and I replaced them throughout.

[excelnewb02]

Maybe I should back up. Here's the original macro I used that worked perfectly (except for preserving the conditional formatting):

Sub Auto_Open()
'
' RiskFilter Macro
' Performs Advanced Filter using criteria hidden in Rows 2-7.
'
' Keyboard Shortcut: Ctrl+Shift+I
'
Sheets("Risks").Select
Rows("9:100").Select
ActiveWindow.SmallScroll Down:=-111
Selection.Delete Shift:=xlUp
Sheets("2011").Range("A3:Z72").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:Z7"), CopyToRange:=Range("A9"), Unique:=True
End Sub

I've tried to figure out how to use a dynamic named range so that I can apply the conditional formatting after the filter is applied, but I'm afraid that level of VBA is just beyond me without someone really walking me through it. Oh, I also have this code in Sheet1:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Set rng = Range("A1:Z72")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
Call Auto_Open
Sheets("2011").Select
End Sub

[Richard Buttrey]

Hi,

Can you upload the workbook so that we can see the request in context.

Regards

[martindwilson]

and don't forget to add code tags to your code