Find the next Cell with interior color using Do Until...Loop
 

I need a macro to scan down Col. B from the ActiveCell to find the next cell
that is hightlighted orange ( .Interior.ColorIndex = 40). Here is what I
got, but it does not seem to work.

Sub FindLastRow()

Dim LastRow As Long
Dim i As Long

Do
For i = ActiveCell.Row To ActiveCell.Row + 30
LastRow = Cells(i, 2).Row
Next i
Loop Until Cells(i, 2).Interior.ColorIndex = 40

MsgBox "LastRow = " & LastRow

End Sub

Thanks in Advance,
Ryan

Find the next Cell with interior color using Do Until...Loop
 

Sub ryan()
Set r = Range(ActiveCell.Address & ":B65536")
For Each rr In r
If rr.Interior.ColorIndex = 40 Then
MsgBox (rr.Row)
Exit Sub
End If
Next
End Sub

--
Gary''s Student - gsnu200772

Find the next Cell with interior color using Do Until...Loop
 

Possible

Sub FindLastRow()
Dim LastRow As Long
Dim i As Long
For i = ActiveCell.Row To 65536
If Cells(i, 2).Interior.ColorIndex = 40 Then
MsgBox Cells(i, 2).Address
Exit Sub
End If
Next
End Sub

Mike

"RyanH" wrote:

I need a macro to scan down Col. B from the ActiveCell to find the next cell
that is hightlighted orange ( .Interior.ColorIndex = 40). Here is what I
got, but it does not seem to work.

Sub FindLastRow()

Dim LastRow As Long
Dim i As Long

Do
For i = ActiveCell.Row To ActiveCell.Row + 30
LastRow = Cells(i, 2).Row
Next i
Loop Until Cells(i, 2).Interior.ColorIndex = 40

MsgBox "LastRow = " & LastRow

End Sub

Thanks in Advance,
Ryan

Find the next Cell with interior color using Do Until...Loop
 

Thanks for the responses. I failed to mention that this macro is actually
only a small part of a larger macro. I need to only exit the loop, not exit
the sub. Is this possible?

Thanks
Ryan

"Mike H" wrote:

Possible

Sub FindLastRow()
Dim LastRow As Long
Dim i As Long
For i = ActiveCell.Row To 65536
If Cells(i, 2).Interior.ColorIndex = 40 Then
MsgBox Cells(i, 2).Address
Exit Sub
End If
Next
End Sub

Mike

"RyanH" wrote:

I need a macro to scan down Col. B from the ActiveCell to find the next cell
that is hightlighted orange ( .Interior.ColorIndex = 40). Here is what I
got, but it does not seem to work.

Sub FindLastRow()

Dim LastRow As Long
Dim i As Long

Do
For i = ActiveCell.Row To ActiveCell.Row + 30
LastRow = Cells(i, 2).Row
Next i
Loop Until Cells(i, 2).Interior.ColorIndex = 40

MsgBox "LastRow = " & LastRow

End Sub

Thanks in Advance,
Ryan

Find the next Cell with interior color using Do Until...Loop
 

For either Mike's code or my code use:
Exit For
in place of:
Exit Sub
--
Gary''s Student - gsnu200772


"RyanH" wrote:

Thanks for the responses. I failed to mention that this macro is actually
only a small part of a larger macro. I need to only exit the loop, not exit
the sub. Is this possible?

Thanks
Ryan

"Mike H" wrote:

Possible

Sub FindLastRow()
Dim LastRow As Long
Dim i As Long
For i = ActiveCell.Row To 65536
If Cells(i, 2).Interior.ColorIndex = 40 Then
MsgBox Cells(i, 2).Address
Exit Sub
End If
Next
End Sub

Mike

"RyanH" wrote:

I need a macro to scan down Col. B from the ActiveCell to find the next cell
that is hightlighted orange ( .Interior.ColorIndex = 40). Here is what I
got, but it does not seem to work.

Sub FindLastRow()

Dim LastRow As Long
Dim i As Long

Do
For i = ActiveCell.Row To ActiveCell.Row + 30
LastRow = Cells(i, 2).Row
Next i
Loop Until Cells(i, 2).Interior.ColorIndex = 40

MsgBox "LastRow = " & LastRow

End Sub

Thanks in Advance,
Ryan