rick:

just wondering. is this going to be any faster than the for each cell loop i and
others originally posted?

--


Gary


"Rick Rothstein" wrote in message
...
Here is same code, but modified slightly to eliminate the overhead associated
with iterating a range of cells... I changed the loop from a For Each to an
"normal" For..Next loop. I also changed the method of calculating the initial
cell reference assigned to the Found variable (since the first method I used
would not always calculate the last cell in the the range assigned to Rng
correctly)

Sub Test()
Dim X As Long
Dim Rng As Range
Dim Found As Range
Dim AllSize18Cells As Range
Dim FirstRange As String
Set Rng = Range("A24")
' Start with the last cell in the range so that
' the addresses will list in ascending order.
Set Found = Range(Split(Rng.Address & ":" & Rng.Address, ":")(1))
Application.FindFormat.Font.Size = 18
For X = 1 To Rng.Count
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)


"Rick Rothstein" wrote in message
...
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