Is it possible to just act on the filtered data

  • Thread starter Thread starter John Menken
  • Start date Start date
J

John Menken

Hi, I'm using Excel 2010 and I have a question about filtered data and
performing an operation on just the filtered data. In the code below I
have two filters running. Then I have the code moving down the column
turning text red when certain criteria are met. The problem is that it
is doing it on **all** the data in the YOS column and I only want it
to do it on the filtered data. Is there a way to adjust my code so
that it only operates on filtered data? Thanks.

'Set Region filter to NA and sets Work Location Data filter to all US
locations
Selection.AutoFilter Field:=11, Criteria1:="NA"
Selection.AutoFilter Field:=10
'Formats text in YOS column to red if >= 1
finalrow = Cells(200, 1).End(xlUp).Row
For i = 2 To finalrow
If Cells(i, 13).Value >= 1 Then
Cells(i, 13).Font.Color = -16776961
End If
Next i
 
Hi, I'm using Excel 2010 and I have a question about filtered data and
performing an operation on just the filtered data. In the code below I
have two filters running. Then I have the code moving down the column
turning text red when certain criteria are met. The problem is that it
is doing it on **all** the data in the YOS column and I only want it
to do it on the filtered data. Is there a way to adjust my code so
that it only operates on filtered data? Thanks.

'Set Region filter to NA and sets Work Location Data filter to all US
locations
    Selection.AutoFilter Field:=11, Criteria1:="NA"
    Selection.AutoFilter Field:=10
    'Formats text in YOS column to red if >= 1
        finalrow = Cells(200, 1).End(xlUp).Row
        For i = 2 To finalrow
        If Cells(i, 13).Value >= 1 Then
            Cells(i, 13).Font.Color = -16776961
        End If
        Next i
Click to expand...

Just ALSO filter by the value for column 13 and then use
..specialcells(xlcelltypevisible).font>>>>
 
Thanks. I took your suggestion and it works fine except for one little
thing.
The code below also formats the titles (row 1) in red. Is there any
way to modify the code below so that it ignores the titles?
Thanks for all your help.

....
'Set Region filter to NA and sets Work Location Data filter to all US
locations
'also sets an auto filter to YOS column
Selection.AutoFilter Field:=11, Criteria1:="NA"
Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=13, Criteria1:=">=1"
'Formats rows to red

With Selection.SpecialCells(xlCellTypeVisible)
.Font.Color = -16776961
End With


'removes AutoFilter
Selection.AutoFilter

....
 
Back
Top