Finding a formatted cell with VBA

[JDerrico]

Good evening,
I'm new here and I know just enough Excel VBA to get by.

However, I'm stumped with this. Any and all help would be greatly appreciated and I thank you in advance for your help.

What I would like to do is, find the first Blue (color 41) cell using the Find function in Excel. I can do that and it works fine, but I wanted to have it done in a macro.

Here's the code that Excel created:

Sub OL_Find_BlueFormat1()
'
' OL_Find_BlueFormat1 Macro
' Macro recorded 7/27/2006 by Jay D.
'
'
Range("B5").Select
With Application.FindFormat.Interior
.ColorIndex = 41
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True).Activate
ActiveCell.Offset(-1, 0).Range("A1:AB1").Select
Range(Selection, Selection.End(xlUp)).Select
End Sub

The problem is that it gets stuck on this part when I rerun the macro.

Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True).Activate

Any ideas how to fix this?

Thanks, Jay

[jindon]

Hi
It is always better to have object variable to check if the cell is found when you use Find Method, like this...

Sub OL_Find_BlueFormat1()
'
' OL_Find_BlueFormat1 Macro
' Macro recorded 7/27/2006 by Jay D.
'
Dim r As Range
With Application.FindFormat.Interior
.ColorIndex = 41
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Set r = Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True)
If Not r Is Nothing Then
r.Offset(-1, 0).Range("A1:AB1").Select
Range(Selection, Selection.End(xlUp)).Select
Else
MegBox "Not Found"
End If
End Sub

[JDerrico]

Thanks for replying. I tried it and still got stuck, but I'll try again from work (on Monday) where I will be using the code.

Not sure if this makes any difference, but I am using Excel 2003 at work, and I tried your code (from church) on Excel 2000.

Again, thank you.

Jay

[Cyberdude]

You work on Excel at church?? Hmmmmm.

[JDerrico]

well, it was after church.

[jindon]

I think you can search fomat from 2002...

If you are on 2000 or earlier, I think you need to loop through the cells....