# For/Next statement problem

#### Karlbirger

##### Board Regular
Hi guys,

I have written the below code (thank You Andrew for this) but when I try to select a whole row (A:AM) when any cell in column A6:A & LastRow2 equals "Finished" there is a run-time error.

If the requirement is fulfilled, the column A:AM on that specific row shall change colour.

I dont know if this is a correct code to use, but I think so. I get a run-time error on the below red marked code. I have put the code in Worksheet_SelectionChange.

Code:
``````Dim LastRow2 As Long
Dim Cell2 As Range
LastRow2 = Range("A" & Rows.Count).End(xlUp).Row
For Each Cell2 In Range("A6:A" & LastRow2)
If Cell2.Value = "Finished" Then
[B][COLOR=red]Range("A:AM" & Cell2.Row).Select[/COLOR][/B]
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
End With
Else
End If
Next Cell2``````

Using Vista2007 and Excel2007.

Regards
KB

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Range("A" & Cell2.Row & ":AM" & Cell2.Row).Select
or
Cells(Cell2.Row,1).resize(,39).select

better, remove all selecting:

Code:
``````If Cell2.Value = "Finished" Then
[INDENT] With Range("A" & Cell2.Row & ":AM" & Cell2.Row).Interior
[INDENT] .Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
[/INDENT] End With
[/INDENT] End If``````

Last edited:
Hi

Code:
``Range("A" & Cell2.Row & ":AM" & Cell2.Row).Select``

This works, but if a cell in column A now equals "Finished", the marker won't let go of that cell. Like it's looping itself to select the cell that equals "Finished" in column A.

Any input what can be wrong?

Regards
KB

Thanks alot.

Regards
KB

Replies
9
Views
166
Replies
5
Views
484
Replies
2
Views
99
Replies
0
Views
195
Replies
3
Views
188

1,203,523
Messages
6,055,895
Members
444,832
Latest member
Kauri

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back