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


"Dave Peterson" wrote:

Hope it worked for the OP, though <bg.

Gord Dibben wrote:

Whew!

Thanks Dave.

Gord

On Thu, 25 Oct 2007 17:16:04 -0500, Dave Peterson
wrote:

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


Horatio J. Bilge, Jr. wrote:

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" wrote:

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


On Thu, 25 Oct 2007 12:38:02 -0700, Horatio J. Bilge, Jr.
wrote:

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



--

Dave Peterson