.Find cell within rng when font color=3
Select your range while you're recording:
I got this:
Columns("C:C").Select
With Application.FindFormat.Font
.ColorIndex = 3
End With
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate
I'd edit it to:
Option Explicit
Sub testme()
Dim myRng As Range
Dim FoundCell As Range
Dim myVar As Variant
With Worksheets("Sheet1")
Set myRng = .Range("c1").EntireColumn
End With
With Application.FindFormat
.Clear
.Font.ColorIndex = 3
End With
With myRng
Set FoundCell = .Cells.Find(What:="", _
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True)
End With
If FoundCell Is Nothing Then
MsgBox "Not found!"
Else
myVar = FoundCell.Value
MsgBox myVar
End If
End Sub
Jim at Eagle wrote:
Thanks for your help got this and no errors.
Sub FindColor()
With Application.FindFormat.Font
.ColorIndex = 3
End With
End Sub
But: I need to limit the search to a range of cells and retrieve and store
the find in a variable.
--
Jim at Eagle
"Dave Peterson" wrote:
What version of excel are you using?
xl2002 (IIRC) has the ability to search for formats.
If you're using a newer version of excel, you can record a macro when you do the
edit|Find manually.
If you're using an older version of excel, I think you'll be looping.
Jim at Eagle wrote:
I have a loop that looks at each cell and if color is 3 then place cell value
in variable. I need to speed up the process. There is only one cell that
meets the criteria just hate look at all cells.
Tried many ways nothing working. Please help.
--
Jim at Eagle
--
Dave Peterson
--
Dave Peterson
|