PDA

View Full Version : Format selected cells within filtered range



Glaswegian
07-08-2009, 04:00 AM
Hi

I've obviously left my brain at home today...:dunno

I'm filtering a list (about 2000 rows) using 3 criteria. This leaves me with a list of about 17-20 rows. I'm trying to colour the font in the first 10 rows in one column but this is where my brain has departed - I can colour all 17 cells in this column, but not the first 10. Can anyone put me out my misery?

Sub FormatRatesSheet()
Dim myRng As Range
Dim x As Long
Application.ScreenUpdating = False
Set myRng = ActiveSheet.Range("M2", Range("M65536").End(xlUp))
With ActiveSheet
.Range("C1").AutoFilter FIELD:=3, Criteria1:="GBP"
.Range("C1").AutoFilter FIELD:=2, Criteria1:="SWAP"
.Range("C1").AutoFilter FIELD:=9, Criteria1:="=gbp*", Operator:=xlAnd
End With
myRng.SpecialCells(xlCellTypeVisible).Font.ColorIndex = 3
Application.ScreenUpdating = True
End Sub
Thanks in advance.

Bob Phillips
07-08-2009, 04:14 AM
I hate to say it, but I think yoou have to loop



Sub FormatRatesSheet()
Dim myRng As Range
Dim mpArea As Range
Dim cell As Range
Dim cnt As Long
Dim x As Long

Application.ScreenUpdating = False
Set myRng = ActiveSheet.Range("M2", Range("M65536").End(xlUp))
With ActiveSheet
.Range("C1").AutoFilter FIELD:=3, Criteria1:="GBP"
.Range("C1").AutoFilter FIELD:=2, Criteria1:="SWAP"
.Range("C1").AutoFilter FIELD:=9, Criteria1:="=gbp*", Operator:=xlAnd
End With
With myRng.SpecialCells(xlCellTypeVisible)

For Each mpArea In .Areas

For Each cell In mpArea.Cells

cnt = cnt + 1
If cnt <= 10 Then

cell.Font.ColorIndex = 3
End If
Next cell
Next mpArea
End With

Application.ScreenUpdating = True
End Sub

Glaswegian
07-08-2009, 04:31 AM
Hi xld

Yes, that makes sense - and works perfectly.

Many thanks.