Below is part of my code from a macro that worked fine in 2003, but does not
work the same in 2007. In 2003, the code would filter a certain field and
change the interior cell color of only the visibile cells with in the
selection. In 2007, it changes the color of all the cells with in the range
specified (A6:AC3610). Why is this different in 2007 and how do I correct
it. Any help is greatly appreciated. Thanks.

Range("A6:ac3610").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($A6<>"""",IF($G6=2,$aa6>=1.1,$aa6>0))"
Selection.FormatConditions(1).Font.ColorIndex = 50
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF($A6<>"""",IF($G6=2,$aa6<2,$aa6<1))"
Selection.FormatConditions(2).Interior.ColorIndex = 3


' Filters spread sheet

Selection.AutoFilter Field:=27, Criteria1:="<1.1", Operator:=xlAnd

' Changes color on visible cells within my range

With Selection.Interior
.ColorIndex = 45
.Pattern = xlSolid
End With

I'm guessing this isn't the only inconsistency you'll find between 2003 and
2007. If I were you, I'd record a small macro in 2007 to do the same thing
and change your code accordingly.
--
HTH,
Barb Reinhardt




"GregK" wrote:

> Below is part of my code from a macro that worked fine in 2003, but does not
> work the same in 2007. In 2003, the code would filter a certain field and
> change the interior cell color of only the visibile cells with in the
> selection. In 2007, it changes the color of all the cells with in the range
> specified (A6:AC3610). Why is this different in 2007 and how do I correct
> it. Any help is greatly appreciated. Thanks.
>
> Range("A6:ac3610").Select
> Selection.FormatConditions.Delete
> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=IF($A6<>"""",IF($G6=2,$aa6>=1.1,$aa6>0))"
> Selection.FormatConditions(1).Font.ColorIndex = 50
> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=IF($A6<>"""",IF($G6=2,$aa6<2,$aa6<1))"
> Selection.FormatConditions(2).Interior.ColorIndex = 3
>
>
> ' Filters spread sheet
>
> Selection.AutoFilter Field:=27, Criteria1:="<1.1", Operator:=xlAnd
>
> ' Changes color on visible cells within my range
>
> With Selection.Interior
> .ColorIndex = 45
> .Pattern = xlSolid
> End With

Interestingly enough, it does the same thing. When I filter and select the
range and change the color it still captures all the cells that I filtered
out.

"Barb Reinhardt" wrote:

> I'm guessing this isn't the only inconsistency you'll find between 2003 and
> 2007. If I were you, I'd record a small macro in 2007 to do the same thing
> and change your code accordingly.
> --
> HTH,
> Barb Reinhardt
>
>
>
>
> "GregK" wrote:
>
> > Below is part of my code from a macro that worked fine in 2003, but does not
> > work the same in 2007. In 2003, the code would filter a certain field and
> > change the interior cell color of only the visibile cells with in the
> > selection. In 2007, it changes the color of all the cells with in the range
> > specified (A6:AC3610). Why is this different in 2007 and how do I correct
> > it. Any help is greatly appreciated. Thanks.
> >
> > Range("A6:ac3610").Select
> > Selection.FormatConditions.Delete
> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> > "=IF($A6<>"""",IF($G6=2,$aa6>=1.1,$aa6>0))"
> > Selection.FormatConditions(1).Font.ColorIndex = 50
> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> > "=IF($A6<>"""",IF($G6=2,$aa6<2,$aa6<1))"
> > Selection.FormatConditions(2).Interior.ColorIndex = 3
> >
> >
> > ' Filters spread sheet
> >
> > Selection.AutoFilter Field:=27, Criteria1:="<1.1", Operator:=xlAnd
> >
> > ' Changes color on visible cells within my range
> >
> > With Selection.Interior
> > .ColorIndex = 45
> > .Pattern = xlSolid
> > End With

It seems to work for me in 2007. In what way do you feel that it is not
working?

--
__________________________________
HTH

Bob

"GregK" <(E-Mail Removed)> wrote in message
news8A72D93-8CD4-443C-BB77-(E-Mail Removed)...
> Below is part of my code from a macro that worked fine in 2003, but does
> not
> work the same in 2007. In 2003, the code would filter a certain field and
> change the interior cell color of only the visibile cells with in the
> selection. In 2007, it changes the color of all the cells with in the
> range
> specified (A6:AC3610). Why is this different in 2007 and how do I correct
> it. Any help is greatly appreciated. Thanks.
>
> Range("A6:ac3610").Select
> Selection.FormatConditions.Delete
> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=IF($A6<>"""",IF($G6=2,$aa6>=1.1,$aa6>0))"
> Selection.FormatConditions(1).Font.ColorIndex = 50
> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=IF($A6<>"""",IF($G6=2,$aa6<2,$aa6<1))"
> Selection.FormatConditions(2).Interior.ColorIndex = 3
>
>
> ' Filters spread sheet
>
> Selection.AutoFilter Field:=27, Criteria1:="<1.1", Operator:=xlAnd
>
> ' Changes color on visible cells within my range
>
> With Selection.Interior
> .ColorIndex = 45
> .Pattern = xlSolid
> End With

When I run it in 2007, all of the cells within the range a6:ac3610 filtered
or not turn to orange. When I run the code in 2003, only the cells that are
visible through the autofilter in range a6:a3610 turn orange.

"Bob Phillips" wrote:

> It seems to work for me in 2007. In what way do you feel that it is not
> working?
>
> --
> __________________________________
> HTH
>
> Bob
>
> "GregK" <(E-Mail Removed)> wrote in message
> news8A72D93-8CD4-443C-BB77-(E-Mail Removed)...
> > Below is part of my code from a macro that worked fine in 2003, but does
> > not
> > work the same in 2007. In 2003, the code would filter a certain field and
> > change the interior cell color of only the visibile cells with in the
> > selection. In 2007, it changes the color of all the cells with in the
> > range
> > specified (A6:AC3610). Why is this different in 2007 and how do I correct
> > it. Any help is greatly appreciated. Thanks.
> >
> > Range("A6:ac3610").Select
> > Selection.FormatConditions.Delete
> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> > "=IF($A6<>"""",IF($G6=2,$aa6>=1.1,$aa6>0))"
> > Selection.FormatConditions(1).Font.ColorIndex = 50
> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> > "=IF($A6<>"""",IF($G6=2,$aa6<2,$aa6<1))"
> > Selection.FormatConditions(2).Interior.ColorIndex = 3
> >
> >
> > ' Filters spread sheet
> >
> > Selection.AutoFilter Field:=27, Criteria1:="<1.1", Operator:=xlAnd
> >
> > ' Changes color on visible cells within my range
> >
> > With Selection.Interior
> > .ColorIndex = 45
> > .Pattern = xlSolid
> > End With
>
>
>

How can you tell if they are not visible?

--
__________________________________
HTH

Bob

"GregK" <(E-Mail Removed)> wrote in message
news:809BF1A7-0016-41F0-B8BE-(E-Mail Removed)...
> When I run it in 2007, all of the cells within the range a6:ac3610
> filtered
> or not turn to orange. When I run the code in 2003, only the cells that
> are
> visible through the autofilter in range a6:a3610 turn orange.
>
> "Bob Phillips" wrote:
>
>> It seems to work for me in 2007. In what way do you feel that it is not
>> working?
>>
>> --
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "GregK" <(E-Mail Removed)> wrote in message
>> news8A72D93-8CD4-443C-BB77-(E-Mail Removed)...
>> > Below is part of my code from a macro that worked fine in 2003, but
>> > does
>> > not
>> > work the same in 2007. In 2003, the code would filter a certain field
>> > and
>> > change the interior cell color of only the visibile cells with in the
>> > selection. In 2007, it changes the color of all the cells with in the
>> > range
>> > specified (A6:AC3610). Why is this different in 2007 and how do I
>> > correct
>> > it. Any help is greatly appreciated. Thanks.
>> >
>> > Range("A6:ac3610").Select
>> > Selection.FormatConditions.Delete
>> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
>> > "=IF($A6<>"""",IF($G6=2,$aa6>=1.1,$aa6>0))"
>> > Selection.FormatConditions(1).Font.ColorIndex = 50
>> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
>> > "=IF($A6<>"""",IF($G6=2,$aa6<2,$aa6<1))"
>> > Selection.FormatConditions(2).Interior.ColorIndex = 3
>> >
>> >
>> > ' Filters spread sheet
>> >
>> > Selection.AutoFilter Field:=27, Criteria1:="<1.1", Operator:=xlAnd
>> >
>> > ' Changes color on visible cells within my range
>> >
>> > With Selection.Interior
>> > .ColorIndex = 45
>> > .Pattern = xlSolid
>> > End With
>>
>>
>>

I can tell once I unfilter the selection.

"Bob Phillips" wrote:

> How can you tell if they are not visible?
>
> --
> __________________________________
> HTH
>
> Bob
>
> "GregK" <(E-Mail Removed)> wrote in message
> news:809BF1A7-0016-41F0-B8BE-(E-Mail Removed)...
> > When I run it in 2007, all of the cells within the range a6:ac3610
> > filtered
> > or not turn to orange. When I run the code in 2003, only the cells that
> > are
> > visible through the autofilter in range a6:a3610 turn orange.
> >
> > "Bob Phillips" wrote:
> >
> >> It seems to work for me in 2007. In what way do you feel that it is not
> >> working?
> >>
> >> --
> >> __________________________________
> >> HTH
> >>
> >> Bob
> >>
> >> "GregK" <(E-Mail Removed)> wrote in message
> >> news8A72D93-8CD4-443C-BB77-(E-Mail Removed)...
> >> > Below is part of my code from a macro that worked fine in 2003, but
> >> > does
> >> > not
> >> > work the same in 2007. In 2003, the code would filter a certain field
> >> > and
> >> > change the interior cell color of only the visibile cells with in the
> >> > selection. In 2007, it changes the color of all the cells with in the
> >> > range
> >> > specified (A6:AC3610). Why is this different in 2007 and how do I
> >> > correct
> >> > it. Any help is greatly appreciated. Thanks.
> >> >
> >> > Range("A6:ac3610").Select
> >> > Selection.FormatConditions.Delete
> >> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> >> > "=IF($A6<>"""",IF($G6=2,$aa6>=1.1,$aa6>0))"
> >> > Selection.FormatConditions(1).Font.ColorIndex = 50
> >> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> >> > "=IF($A6<>"""",IF($G6=2,$aa6<2,$aa6<1))"
> >> > Selection.FormatConditions(2).Interior.ColorIndex = 3
> >> >
> >> >
> >> > ' Filters spread sheet
> >> >
> >> > Selection.AutoFilter Field:=27, Criteria1:="<1.1", Operator:=xlAnd
> >> >
> >> > ' Changes color on visible cells within my range
> >> >
> >> > With Selection.Interior
> >> > .ColorIndex = 45
> >> > .Pattern = xlSolid
> >> > End With
> >>
> >>
> >>
>
>
>

Anyone have any ideas why this is working differently in 2007? I thought I
came across an early thread that mentioned something about 2007 handeling
"with selection" differently than previous versions?

"GregK" wrote:

> I can tell once I unfilter the selection.
>
> "Bob Phillips" wrote:
>
> > How can you tell if they are not visible?
> >
> > --
> > __________________________________
> > HTH
> >
> > Bob
> >
> > "GregK" <(E-Mail Removed)> wrote in message
> > news:809BF1A7-0016-41F0-B8BE-(E-Mail Removed)...
> > > When I run it in 2007, all of the cells within the range a6:ac3610
> > > filtered
> > > or not turn to orange. When I run the code in 2003, only the cells that
> > > are
> > > visible through the autofilter in range a6:a3610 turn orange.
> > >
> > > "Bob Phillips" wrote:
> > >
> > >> It seems to work for me in 2007. In what way do you feel that it is not
> > >> working?
> > >>
> > >> --
> > >> __________________________________
> > >> HTH
> > >>
> > >> Bob
> > >>
> > >> "GregK" <(E-Mail Removed)> wrote in message
> > >> news8A72D93-8CD4-443C-BB77-(E-Mail Removed)...
> > >> > Below is part of my code from a macro that worked fine in 2003, but
> > >> > does
> > >> > not
> > >> > work the same in 2007. In 2003, the code would filter a certain field
> > >> > and
> > >> > change the interior cell color of only the visibile cells with in the
> > >> > selection. In 2007, it changes the color of all the cells with in the
> > >> > range
> > >> > specified (A6:AC3610). Why is this different in 2007 and how do I
> > >> > correct
> > >> > it. Any help is greatly appreciated. Thanks.
> > >> >
> > >> > Range("A6:ac3610").Select
> > >> > Selection.FormatConditions.Delete
> > >> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> > >> > "=IF($A6<>"""",IF($G6=2,$aa6>=1.1,$aa6>0))"
> > >> > Selection.FormatConditions(1).Font.ColorIndex = 50
> > >> > Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> > >> > "=IF($A6<>"""",IF($G6=2,$aa6<2,$aa6<1))"
> > >> > Selection.FormatConditions(2).Interior.ColorIndex = 3
> > >> >
> > >> >
> > >> > ' Filters spread sheet
> > >> >
> > >> > Selection.AutoFilter Field:=27, Criteria1:="<1.1", Operator:=xlAnd
> > >> >
> > >> > ' Changes color on visible cells within my range
> > >> >
> > >> > With Selection.Interior
> > >> > .ColorIndex = 45
> > >> > .Pattern = xlSolid
> > >> > End With
> > >>
> > >>
> > >>
> >
> >
> >

Well, I figured it out on my own. I had to change

With Selection.Interior

to

With Range("A6:ac3610").Interior

It now will only color the cells within that range that visible through the
autofilter. How can I get to only color the table has a variable range of
visibile cells? For example, the table usually ends around 2000 rows, but I
added the extra beef of 3610 to be safe. I want to only select what's in the
autofitler and always leave out the headers (row 6).

"GregK" wrote:

> Below is part of my code from a macro that worked fine in 2003, but does not
> work the same in 2007. In 2003, the code would filter a certain field and
> change the interior cell color of only the visibile cells with in the
> selection. In 2007, it changes the color of all the cells with in the range
> specified (A6:AC3610). Why is this different in 2007 and how do I correct
> it. Any help is greatly appreciated. Thanks.
>
> Range("A6:ac3610").Select
> Selection.FormatConditions.Delete
> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=IF($A6<>"""",IF($G6=2,$aa6>=1.1,$aa6>0))"
> Selection.FormatConditions(1).Font.ColorIndex = 50
> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=IF($A6<>"""",IF($G6=2,$aa6<2,$aa6<1))"
> Selection.FormatConditions(2).Interior.ColorIndex = 3
>
>
> ' Filters spread sheet
>
> Selection.AutoFilter Field:=27, Criteria1:="<1.1", Operator:=xlAnd
>
> ' Changes color on visible cells within my range
>
> With Selection.Interior
> .ColorIndex = 45
> .Pattern = xlSolid
> End With

The proper way to do it is to create a range variable that identifies the
data table range, filter that, set a new range variable to the visible rows,
and work upon that.

--
__________________________________
HTH

Bob

"GregK" <(E-Mail Removed)> wrote in message
news1C68B96-AB35-4B21-B7C9-(E-Mail Removed)...
> Well, I figured it out on my own. I had to change
>
> With Selection.Interior
>
> to
>
> With Range("A6:ac3610").Interior
>
> It now will only color the cells within that range that visible through
> the
> autofilter. How can I get to only color the table has a variable range of
> visibile cells? For example, the table usually ends around 2000 rows, but
> I
> added the extra beef of 3610 to be safe. I want to only select what's in
> the
> autofitler and always leave out the headers (row 6).
>
> "GregK" wrote:
>
>> Below is part of my code from a macro that worked fine in 2003, but does
>> not
>> work the same in 2007. In 2003, the code would filter a certain field
>> and
>> change the interior cell color of only the visibile cells with in the
>> selection. In 2007, it changes the color of all the cells with in the
>> range
>> specified (A6:AC3610). Why is this different in 2007 and how do I
>> correct
>> it. Any help is greatly appreciated. Thanks.
>>
>> Range("A6:ac3610").Select
>> Selection.FormatConditions.Delete
>> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
>> "=IF($A6<>"""",IF($G6=2,$aa6>=1.1,$aa6>0))"
>> Selection.FormatConditions(1).Font.ColorIndex = 50
>> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
>> "=IF($A6<>"""",IF($G6=2,$aa6<2,$aa6<1))"
>> Selection.FormatConditions(2).Interior.ColorIndex = 3
>>
>>
>> ' Filters spread sheet
>>
>> Selection.AutoFilter Field:=27, Criteria1:="<1.1", Operator:=xlAnd
>>
>> ' Changes color on visible cells within my range
>>
>> With Selection.Interior
>> .ColorIndex = 45
>> .Pattern = xlSolid
>> End With