refer to cell relative to range

[Guest]

I have a worksheet shere I periodically update the data in a range. The
column to the right of the range displays the date when I last updated that
row of data (see code below). I would like to change the code so it will do
the same thing with whatever named range I include.

This is what I have:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
With WS.Cells(Target.Row, 13)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End If
End Sub

This is what I tried. I thought this way I could easily add additional
ranges as ElseIf statements. I get an error, "Argument is not optional."
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, Range("Skill1")) Is Nothing) Then
GoTo Revise_Date
ElseIf Not (Application.Intersect(Target, Range("Skill2")) Is Nothing) Then
GoTo Revise_Date
Else: Exit Sub
End If

Revise_Date:
With WS.Cells(Target.Row, Target.Range.Offset(0,1))
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
Exit Sub
End Sub

 

[Guest]

just create the combined range with Union()

set united=Union(Range("Skill1"),Range("Skill2"))
If Not (Application.Intersect(Target, united) Is Nothing) Then

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
If Not (Application.Intersect(Target, _
Range("skill1,skill2,skill3")) Is Nothing) Then
On Error GoTo endit
Application.EnableEvents = False
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With
End If
endit:
Application.EnableEvents = True
End Sub

The first two lines are not needed IMO

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")


Gord Dibben MS Excel MVP

 

[Guest]

This places the date one cell to the right of the cell I edit. I want the
date to go one cell to the right of the range. For example, for
range("Skill1")="A1:E20" if I edit A2 (or B2, C2, etc.), the date should go
in F2. And for "Skill2"="G1:Y20" if I edit G2, the date should go in Z2.

 

[Gord Dibben]

Getting a bit beyond my skills.

I'll work on it but probably do what you would do and that is go through VB
help.

Hopefully someone can jump in before we're forced to do that<g>


Gord

 

[Dave Peterson]

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCol As Long

'only one cell at a time
If Target.Cells.Count > 1 Then Exit Sub

If Not (Application.Intersect(Target, Me.Range("skill1")) Is Nothing) Then
'in Skill1
With Me.Range("skill1")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill2")) Is Nothing) Then
'in Skill2
With Me.Range("skill2")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill3")) Is Nothing) Then
'in Skill3
With Me.Range("skill3")
myCol = .Columns(.Columns.Count).Column + 1
End With
Else
myCol = 0
End If

If myCol = 0 Then Exit Sub

On Error GoTo endit
Application.EnableEvents = False
With Me.Cells(Target.Row, myCol)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With

endit:
Application.EnableEvents = True
End Sub

 

[Gord Dibben]

Whew!

Thanks Dave.

Gord

Maybe...

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCol As Long

'only one cell at a time
If Target.Cells.Count > 1 Then Exit Sub

If Not (Application.Intersect(Target, Me.Range("skill1")) Is Nothing) Then
'in Skill1
With Me.Range("skill1")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill2")) Is Nothing) Then
'in Skill2
With Me.Range("skill2")
myCol = .Columns(.Columns.Count).Column + 1
End With
ElseIf Not (Application.Intersect(Target, _
Me.Range("skill3")) Is Nothing) Then
'in Skill3
With Me.Range("skill3")
myCol = .Columns(.Columns.Count).Column + 1
End With
Else
myCol = 0
End If

If myCol = 0 Then Exit Sub

On Error GoTo endit
Application.EnableEvents = False
With Me.Cells(Target.Row, myCol)
.Value = Now
.NumberFormat = "mm/dd/yyyy"
End With

endit:
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

Hope it worked for the OP, though <bg>.

Whew!

Thanks Dave.

Gord

 

[Guest]

Just tried it, and it works great.
Thanks!

Hope it worked for the OP, though <bg>.

 

[Guest]

This might be asking too much, but...
Is there a way that I can programmatically add a new ElseIf statement
automatically whenever I define a new range?
~ Horatio