Combining 2 worksheet change events

[vwilliam]

Hello, need help with combining these 2 worksheet change events. Tried combining both under 1 worksheet change, didn't work. Anyways, here's the code. Please help, what's wrong?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim MyRange As Range
Dim c As Range
Dim MyRange2 As Range
Dim c2 As Range

Set MyRange = Intersect(Target, Range("K5:K284"))
Set MyRange2 = Intersect(Target, Range("C5:K284"))

If Not MyRange Is Nothing Then
Application.EnableEvents = False
For Each c In MyRange
If IsEmpty(c.Value) Then
c.Offset(0, 1).ClearContents
Else
With c.Offset(0, 1)
.NumberFormat = "dd mmmm yyyy"
.Value = Date
End With
End If
Next c
Application.EnableEvents = True
End If

If Not MyRange2 Is Nothing Then
Application.EnableEvents = False
For Each c2 In MyRange
If IsEmpty(c2.Value) Then
c.Offset(0, -1).ClearContents
Else
With c.Offset(0, -1)
.NumberFormat = "dd mmmm yyyy"
.Value = Date
End With
End If
Next c
Application.EnableEvents = True
End If



End Sub

 

[Fishboy]

Hello, need help with combining these 2 worksheet change events. Tried combining both under 1 worksheet change, didn't work. Anyways, here's the code. Please help, what's wrong?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim MyRange As Range
Dim c As Range
Dim MyRange2 As Range
Dim c2 As Range

Set MyRange = Intersect(Target, Range("K5:K284"))
Set MyRange2 = Intersect(Target, Range("C5:K284"))

If Not MyRange Is Nothing Then
Application.EnableEvents = False
For Each c In MyRange
If IsEmpty(c.Value) Then
c.Offset(0, 1).ClearContents
Else
With c.Offset(0, 1)
.NumberFormat = "dd mmmm yyyy"
.Value = Date
End With
End If
Next c
Application.EnableEvents = True
End If

If Not MyRange2 Is Nothing Then
Application.EnableEvents = False
For Each c2 In MyRange
If IsEmpty(c2.Value) Then
c.Offset(0, -1).ClearContents
Else
With c.Offset(0, -1)
.NumberFormat = "dd mmmm yyyy"
.Value = Date
End With
End If
Next c
Application.EnableEvents = True
End If



End Sub

Hi vwilliam, welcome to the boards.

At first glance without reading it in any detail to follow what the code is actually doing, what I see is that in the second half of the code is should probably read this:

For Each c2 In MyRange2

Instead of this:

For Each c2 In MyRange

[EDIT] - In fact the whole second section would need a tweak as follows:

If Not MyRange2 Is Nothing Then
Application.EnableEvents = False
For Each c2 In MyRange2
If IsEmpty(c2.Value) Then
c2.Offset(0, -1).ClearContents
Else
With c2.Offset(0, -1)
.NumberFormat = "dd mmmm yyyy"
.Value = Date
End With
End If
Next c2
Application.EnableEvents = True
End If

 

[steve the fish]

You then use c a few times instead of c2. Also im not sure MyRange2 is correct. Is I meant to be C5:K284?

 

[Fishboy]

Also im not sure MyRange2 is correct. Is I meant to be C5:K284?

Wouldn't that make MyRange and MyRange2 identical?

 

[steve the fish]

They say this at the minute. I think C5:K284 will probably be C5:C284.

Set MyRange = Intersect(Target, Range("K5:K284"))
 Set MyRange2 = Intersect(Target, Range("C5:K284"))

 

[vwilliam]

Hi, yes, there were few typos (wrote the codes in a haste). But I did change both the c to c2 s and the range C5:C284... still didn't do it unfortunately.

 

[Fishboy]

Hi, yes, there were few typos (wrote the codes in a haste). But I did change both the c to c2 s and the range C5:C284... still didn't do it unfortunately.

Can you describe in words exactly what you expect each half of the code to be doing?

 

[vwilliam]

You then use c a few times instead of c2. Also im not sure MyRange2 is correct. Is I meant to be C5:K284?

Hi Steve, somehow my problem got solved. I quit the debug mode, closed excel, and reopened. The codes worked. So is it best practice to quit excel after any change in coding to worksheet change?

Thanks again

 

[steve the fish]

Your code probably quit halfway through. This means enableevents is still false. In the immediate window type 'Application.EnableEvents = True' and press enter. Should work then. You don't need to restart excel.