conditional formatting when filtering

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

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)


"be_insane" <be_insane@yahoo.com> wrote in message
news:1130511925.292015.67340@f14g2000cwb.googlegroups.com...
> 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
>

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.

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)


"be_insane" <be_insane@yahoo.com> wrote in message
news:1130859801.695581.78780@f14g2000cwb.googlegroups.com...
> 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.
>

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

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)


"be_insane" <be_insane@yahoo.com> wrote in message
news:1130923109.382753.72120@z14g2000cwz.googlegroups.com...
> 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
>