So then something like this should find all the required cells I would
guess...
Sub Test()
Dim R As Range
Dim Rng As Range
Dim Found As Range
Dim AllSize18Cells As Range
Dim FirstRange As String
Set Rng = Range("A1:A100")
Set Found = Cells(Rng.Count, Rng.Column)
Application.FindFormat.Font.Size = 18
For Each R In Rng
Set Found = Rng.Find(After:=Found, What:="", MatchCase:=False, _
LookIn:=xlFormulas, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, LookAt:=xlPart, _
SearchFormat:=True)
If Found.Address = FirstRange Then Exit For
If AllSize18Cells Is Nothing Then
Set AllSize18Cells = Found
FirstRange = Found.Address
Else
Set AllSize18Cells = Union(AllSize18Cells, Found)
End If
Next
MsgBox AllSize18Cells.Address
End Sub
--
Rick (MVP - Excel)
"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
not sure if you can do what you want, but this should find the first
instance.
then here's a kb about it.
http://support.microsoft.com/kb/282151
Sub test()
Dim MyRange As Range
Dim rngfound As Range
Set MyRange = Range("A1:P1")
Application.FindFormat.Font.Size = 18
With MyRange
Set rngfound = .Find(after:=Range("A1"), What:="", _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=True)
End With
MsgBox rngfound.Address
End Sub
--
Gary
"John" wrote in message
...
Have a range, Myrange. want to find each cell that has font size 18 in
it.
I've been trying by using the find method. Can't get it right. I did the
record macro thing and got:
----------
Range("A1:I1").Select
With Application.FindFormat.Font
.Size = 18
.Subscript = False
End With
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,_
MatchCase:=False, SearchFormat:=True).Activate
-------------
That's find on the worksheet but in VB it doesn't give me which cell it
is found in so I try:
--------
Dim Found as range, MyRange as Range
Set MyRange = Range(Cells(1,1),Cells(1,18)
MyRange.Select
With Application.FindFormat.Font
.Size = 18
.Subscript = False
End With
Set Found = Myrange.Find(What:="", After =... etc. etc.
--------------
That produces an error. Help
John