FIND in VBA

  • Thread starter Thread starter Jan
  • Start date Start date
J

Jan

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
 
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
 
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 *****
 
Back
Top