find format syntax

V

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
 
D

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.
 
J

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
 
V

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top