.Find cell within rng when font color=3

[Jim at Eagle]

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.

 

[Dave Peterson]

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]

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.

 

[Dave Peterson]

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

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.