For/Next statement problem

Karlbirger

Board Regular
Joined
Sep 21, 2011
Messages
68
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
            .TintAndShade = 0.799981688894314
            .PatternTintAndShade = 0
        End With
    Else
    End If
Next Cell2

Using Vista2007 and Excel2007.

Regards
KB
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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             
.TintAndShade = 0.799981688894314             
.PatternTintAndShade = 0     
[/INDENT] End With 
[/INDENT] End If
 
Last edited:
Upvote 0
Hi

Thanks for answer p45cal.

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
 
Upvote 0

Forum statistics

Threads
1,223,492
Messages
6,172,600
Members
452,466
Latest member
Lynlindsay

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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