brain dead today - resize problem

[Susan]

i'm KNOW this is an easy fix, but i just can't find it this
morning...........

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim rTarget As Range
Dim rColorSet As Range
Dim lRow As Long

lRow = ActiveCell.Row
Set ws = ActiveSheet
Set Target = ws.Range("n" & lRow)
Set rTarget = ws.Range("d" & lRow)
Set rColorSet = ws.Range("a:ak" & lRow).Resize(1, 0)

If Target = "" And rTarget = "" Then
rColorSet.Interior.Color = 2
End If

If Target <> "" And rTarget = "" Then
rColorSet.Interior.Color = 16
End If

If Target <> "" And rTarget <> "" Then
rColorSet.Interior.Color = 2
End If

End Sub


on this row:

Set rColorSet = ws.Range("a:ak" & lRow).Resize(1, 0)

i keep getting a 1004 error - method range of object worksheet railed.
i looked at a zillion .resize posts but i can't see the
error..........
anybody?
thanks a zillion!
susan

 

[Jim Rech]

ws.Range("a:ak" & lRow).

Both the "a" and the "ak" need lrow.

--
Jim
| i'm KNOW this is an easy fix, but i just can't find it this
| morning...........
|
| Private Sub Worksheet_Change(ByVal Target As Range)
|
| Dim ws As Worksheet
| Dim rTarget As Range
| Dim rColorSet As Range
| Dim lRow As Long
|
| lRow = ActiveCell.Row
| Set ws = ActiveSheet
| Set Target = ws.Range("n" & lRow)
| Set rTarget = ws.Range("d" & lRow)
| Set rColorSet = ws.Range("a:ak" & lRow).Resize(1, 0)
|
| If Target = "" And rTarget = "" Then
| rColorSet.Interior.Color = 2
| End If
|
| If Target <> "" And rTarget = "" Then
| rColorSet.Interior.Color = 16
| End If
|
| If Target <> "" And rTarget <> "" Then
| rColorSet.Interior.Color = 2
| End If
|
| End Sub
|
|
| on this row:
|
| Set rColorSet = ws.Range("a:ak" & lRow).Resize(1, 0)
|
| i keep getting a 1004 error - method range of object worksheet railed.
| i looked at a zillion .resize posts but i can't see the
| error..........
| anybody?
| thanks a zillion!
| susan
|

 

[Gary Keramidas]

a row number after the first a.

 

[Bernd]

And 2nd param of Resize() should not be 0, I suggest.

Regards,
Bernd

 

[Susan]

thank you very much, gary & jim!!!
susan

Both the "a" and the "ak" need lrow.

--

| i'm KNOW this is an easy fix, but i just can't find it this
| morning...........
|
| Private Sub Worksheet_Change(ByVal Target As Range)
|
| Dim ws As Worksheet
| Dim rTarget As Range
| Dim rColorSet As Range
| Dim lRow As Long
|
| lRow = ActiveCell.Row
| Set ws = ActiveSheet
| Set Target = ws.Range("n" & lRow)
| Set rTarget = ws.Range("d" & lRow)
| Set rColorSet = ws.Range("a:ak" & lRow).Resize(1, 0)
|
| If Target = "" And rTarget = "" Then
| rColorSet.Interior.Color = 2
| End If
|
| If Target <> "" And rTarget = "" Then
| rColorSet.Interior.Color = 16
| End If
|
| If Target <> "" And rTarget <> "" Then
| rColorSet.Interior.Color = 2
| End If
|
| End Sub
|
|
| on this row:
|
| Set rColorSet = ws.Range("a:ak" & lRow).Resize(1, 0)
|
| i keep getting a 1004 error - method range of object worksheet railed.
| i looked at a zillion .resize posts but i can't see the
| error..........
| anybody?
| thanks a zillion!
| susan
|

 

[Tom Ogilvy]

After you fix the row problem,
I would add that I don't believe you can have zero as an argument to resize.
So either leave one of the arguments blank or make the appropriate
adjustment.

 

[Susan]

thanks, tom & bernd, cuz i was just about to post back after banging
my head against the wall some more!!!!
susan