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

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

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

--
Jim
"Susan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
| 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
|

a row number after the first a.

--


Gary


"Susan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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

Regards,
Bernd

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

On Apr 11, 10:04 am, "Jim Rech" <jrr...@hotmail.com> wrote:
> >>ws.Range("a:ak" & lRow).
>
> Both the "a" and the "ak" need lrow.
>
> --
> Jim"Susan" <bogenex...@aol.com> wrote in message
>
> news:(E-Mail Removed)...
> | 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
> |

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.

--
Regards,
Tom Ogilvy


"Susan" wrote:

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

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


On Apr 11, 10:20 am, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Susan" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -