find format syntax

[Valeria]

Dear experts,
I have a macro trying to look for a value in a range with a specific format
(there may be other cells containing the same value however I want to have
the one written in blue only).
I am not able to find the right syntax for this...

Set p = ShC.Range("a1:A" & LastRow).Find(what:=ShC.Cells(h,
1).Value, lookat:=xlWhole, LookIn:=xlValues, SearchFormat:=?????)

Where the criteria is that the found cell must have .Font.ColorIndex = 5

Many thanks as usual for your great help

Kind regards

 

[Dave Peterson]

Record a macro when you do this manually.

If you don't see where to change the format on that Edit|Find dialog, click the
options button. You'll be able to choose the color you want to find -- as well
as all those other choices.

 

[Jacob Skaria]

If SearchFormat:=True then the find will look for the cell with similar
formats as the search cell or ShC.Cells(h, 1).

If the search cell do not have the coloindex and you still need to have this
condition then you will have to use the loop (as in the earlier post) and
check whether the coloindex of varFound is 3

 

[Valeria]

Thanks. As I like the find method, would there be a way to temporarily change
the format of the search cell to what I need and then come back to a
"memorized" format (=the before format)?
I am having big issues with the speed of my macro with the loops and
herefore I am trying to find a way to speed it up!

Thanks
Best regards

 

[Jacob Skaria]

Try the below

With Application.FindFormat
.Clear
.Font.ColorIndex = 5
End With

Set p = ShC.Range("a1:A" & LastRow).Find(what:=ShC.Cells(h, _
1).Value, lookat:=xlWhole, LookIn:=xlValues, SearchFormat:=True)
MsgBox p.Address