-
Finding a formatted cell with VBA
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:
[vba] 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
[/vba]
The problem is that it gets stuck on this part when I rerun the macro.
[VBA]Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True).Activate[/VBA]
Any ideas how to fix this?
Thanks, Jay
-
Hi
It is always better to have object variable to check if the cell is found when you use Find Method, like this...
[vba]
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[/vba]
-
Re: Find BlueFormat
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
-
You work on Excel at church?? Hmmmmm.
-
well, it was after church.
-
I think you can search fomat from 2002...
If you are on 2000 or earlier, I think you need to loop through the cells....
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules