conditional formatting when filtering

B

be_insane

Ok got a weird Q.

Is there any way to change the formatting on a column when you do a
filter on it? I've been using the auto filter function as there is
fairly easy values to filter on and was just wondering if you filter a
column there was a way to change the formatting on that column so it
stands out?

I.e. change the columns fill or text colour?

Any help much appreciated
B
 
B

Bob Phillips

Here is an idea.

Select the whole column and apply CF with a formula of

=COUNTA(G:G)<>SUBTOTAL(3,G:G)

assuming of course that we are talking about column G.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

be_insane

hum... don't seem to have had too much success with that. added in the
formula but not getting any formatting.

The filter is always on the same thing, Where X appears in a row. so
its a grid system really.
 
B

Bob Phillips

Are you using column G to control the data shown in other columns? This is
the only thing I can think that would cause the formatting not to fire, on
the basis that the only values in column G are X, I can't see why you would
filter G otherwise. I my question is correct, what column(s) is it
controlling?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

be_insane

Sorry probabaly have confused you totally! :)

The spreadsheet is a grid system to tell me which items in column A
appear for certain items.

So we have from coulmns B onwards "X"'s in the cells which correspond
to to the required entries in coulmn A

So...

A B C D E
Word1 X X X
Word2 X X X X
Word3 X X
Word4 X X X
Word5 X X X
Word5 X X

So I might then want to filter column C on the X's, or E on the X's
etc.... to show me the words that apply.

As mention originally on the filter it would be nice if that filtered
column had formatting applied! I have played with the code and can get
it vaguely working, but if i apply the formatting code to the multiple
columns not surprisingly they all format!

This isn't really a mjor problem, just would look nicer for the
users...

Hope that all makes sense :)
B
 
B

Bob Phillips

I was just about to give up when I had an idea.

The problem is that by virtue of the grid, my previous solution of comparing
the visible cells against the cells with values would always be the same, it
is either X or nothing.

My brainwave was remembering a UDF that Tom Ogilvy posted a while back,
shown at the end. The way to use it is to select all the columns and add a
CF formula of =ShowFilter(B1), assuming the CF starts in column B. Big
problem is that it is slow running the UDF against whole columns. A
compromise is to add a row before the existing row 2 with this formula in B2

=showfilter(B1)&CHAR(SUBTOTAL(9,B$3)*0+32)

and copy across. Your CF formula then becomes =LEFT(B$2,2)="No", which seems
acceptable response. You can even hide the new row 2 and it still works
fine. Kinda cool IMO :))

Tom's UDF

Public Function ShowFilter(rng As Range)
Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet

Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function



--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

be_insane

Thanks Bob...

I will have to have a go and see if i can get this working... though
some of it is a bit further on from my current excel skills :)

i take it.. =ShowFilter(B1) "B1" will be replaced with the first cell
to format from?
 
B

Bob Phillips

Yes, that is correct.

If you struggle, feel free to send me the workbook, and I will try and
implement for you.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

be_insane

Bob, you are a star... thankyou ever so much...

It's called trying to please the end users! :)

I will have a bash at it and try to get it working in some form, but
may end up taking you up on the offer!

Regards
Bex
 
B

be_insane

Thanks Bob, that is much appreciated. I'll have a bash at it. but might
end up taking you up on the offer. :)

Bex
 
B

be_insane

ok yes am having problems... so if you don't mind i will send over a
cut down version of the workbook so far :)

B
 
Top