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



--
Valeria

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.

Valeria wrote:

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

--
Valeria

--

Dave Peterson

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

--
Jacob (MVP - Excel)


"Valeria" wrote:

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



--
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
--
Valeria


"Jacob Skaria" wrote:

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

--
Jacob (MVP - Excel)


"Valeria" wrote:

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



--
Valeria

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

--
Jacob (MVP - Excel)


"Valeria" wrote:

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
--
Valeria


"Jacob Skaria" wrote:

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

--
Jacob (MVP - Excel)


"Valeria" wrote:

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



--
Valeria