On Wednesday, January 15, 2014 4:03:52 AM UTC-8, Claus Busch wrote:
Hi Howard,



Am Wed, 15 Jan 2014 03:48:06 -0800 (PST) schrieb L. Howard:



I want to house two different change events in this macro.



The second one at the bottom seems to be okay.

It's the AW6:AW100 range I can't seem to figure out.



try:



Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("AW6:AW100,BI48:BJ65")) Is Nothing _

Or Target.Count 1 Then Exit Sub



Application.EnableEvents = False



Select Case Target.Column

Case 49

If Target.Offset(, -3) = "" Then

If Target 0 Then

Target.Offset(, -3) = Target

End If

End If

Application.EnableEvents = True



Application.EnableEvents = False



Case 61, 62

Cells(Target.Row, 46) = Cells(Target.Row, 49)

End Select

Application.EnableEvents = True

End Sub



Regards

Claus B.

Hi Claus,
Thanks for taking a look.

For some reason it does not fire if I change a cell in AW6:AW100.


This is the code I can run from a button and it works fine.

Sub AWcolToATcol()
Dim c As Range
Dim Lc As Long, lr As Long
Dim Arng As Range

lr = Cells(Rows.count, 49).End(xlUp).Row
Set Arng = Range("AW6:AW" & lr)

For Each c In Arng
If c.Offset(, -3) = "" Then
If c 0 Then
c.Offset(, -3) = c
End If
End If
Next
End Sub


Also, can you do a variable range with a change event macro? Seems I read somewhere you have to declare the range Global in a standard module or something like that.

Howard