FIND in VBA

Hi
I want to create a macro that selects column "A:A", and then "finds" a
cell with colourindex 24.
When I try to record such a macro, there always seems to be an "error"
in the recorded macro.
Can anyone help ?

Hi
try something like:
Sub Find_cell()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = 1 to Lastrow
with Cells(RowNdx, "A")
if .interior.colorindex=24 then
msgbox "found cell"
.select
exit sub
End If
end with
Next RowNdx
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany


Jan wrote:
> Hi
> I want to create a macro that selects column "A:A", and then "finds"
a
> cell with colourindex 24.
> When I try to record such a macro, there always seems to be an
"error"
> in the recorded macro.
> Can anyone help ?

Here's one way:

Sub test3010()
Dim iCell As Range
For Each iCell In Range("A:A")
If iCell.Interior.ColorIndex = 24 Then
iCell.Select
Exit For
End If
Next
End Sub

Alan Beban

Jan wrote:

> Hi
> I want to create a macro that selects column "A:A", and then "finds" a
> cell with colourindex 24.
> When I try to record such a macro, there always seems to be an "error"
> in the recorded macro.
> Can anyone help ?

Thanks !! It works!

eyest@tiscali.be (Jan) wrote in message news:<ba7b2706.0404020640.a0aa923@posting.google.c om>...
> Hi
> I want to create a macro that selects column "A:A", and then "finds" a
> cell with colourindex 24.
> When I try to record such a macro, there always seems to be an "error"
> in the recorded macro.
> Can anyone help ?

Hi Jan

I wouldn't re-invent the wheel. As you must have Excel 2000 or higher,
try

Application.FindFormat.Interior.ColorIndex = 26
Columns(1).Find(What:="", After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Activate
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****