PDA

View Full Version : Weaving Two "If Not Intersecting" in One Worksheet Change



coliervile
03-14-2008, 11:06 AM
How do I put both of these two subs into the same Worksheet Change Event???

If Not Intersect(Target, Sheet("Deleted Data").Range("A2:A1000")) Is Nothing Then
Target.Offset(0, 1) = Format( Now()("dd/mm/yy")





Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("A:A").Column Then
Cells(.Row, "B").Value = Format(Now, "mmm-dd-yyyy hh:mm:ss")
Cells(.Row, "D").NumberFormat = "mmm-dd-yyyy"
Cells(.Row, "E").NumberFormat = "mmm-dd-yyyy"
End If
End With
Next Cell

If Not Intersect(Target, Me.Range("B:B", "E:E")) Is Nothing Then

Me.Columns("A:E").Sort Key1:=Me.Range("D2"), Order1:=xlAscending, _
Key2:=Me.Range("B2"), Order2:=xlAscending, _
Header:=xlYes
End If
End Sub

coliervile
03-14-2008, 11:13 AM
I should add that the Worksheet Change Event is already in worksheet "Deleted Data" and I want to add this new code into it...



If Not Intersect(Target, Sheet("Deleted Data").Range("A2:A1000")) Is Nothing Then
Target.Offset(0, 1) = Format( Now()("dd/mm/yy")

coliervile
03-14-2008, 11:25 AM
I made an error on this code it should be... If cell "A2" is not empty then this cell (F1:F1000) =TODAY() I think the coding is correct???


If Not Intersect(Target, Sheet("Deleted Data").Range("F2:F1000")) Is Nothing Then
Target.Offset(0, 1) = Format( Now()("dd/mm/yy")

Bob Phillips
03-14-2008, 01:03 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

For Each Cell In Target

With Cell

If .Column = Me.Range("A:A").Column Then

Me.Cells(.row, "B").Value = Format(Now, "mmm-dd-yyyy hh:mm:ss")
Me.Cells(.row, "D").NumberFormat = "mmm-dd-yyyy"
Me.Cells(.row, "E").NumberFormat = "mmm-dd-yyyy"
End If
End With
Next Cell

If Not Intersect(Target, Me.Range("B:B", "E:E")) Is Nothing Then

Me.Columns("A:E").Sort Key1:=Me.Range("D2"), Order1:=xlAscending, _
Key2:=Me.Range("B2"), Order2:=xlAscending, _
Header:=xlYes

ElseIf Not Intersect(Target, Me.Range("F2:F1000")) Is Nothing Then

Target.Offset(0, 1) = Format(Now, "dd/mm/yy")
End If
End Sub

coliervile
03-14-2008, 01:25 PM
Bob I may have messed you upwith what I needed, but here's what I need...

I made an error on this code it should be... If cell "A2" is blank then cell "F2" is blank cell "A2" is not empty then this cell cell "F2" =Now(). Does this helpout???

Bob Phillips
03-14-2008, 02:14 PM
try again



Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

For Each Cell In Target

With Cell

If .Column = Me.Range("A:A").Column Then

Me.Cells(.row, "B").Value = Format(Now, "mmm-dd-yyyy hh:mm:ss")
Me.Cells(.row, "D").NumberFormat = "mmm-dd-yyyy"
Me.Cells(.row, "E").NumberFormat = "mmm-dd-yyyy"
End If
End With
Next Cell

If Not Intersect(Target, Me.Range("B:B", "E:E")) Is Nothing Then

Me.Columns("A:E").Sort Key1:=Me.Range("D2"), Order1:=xlAscending, _
Key2:=Me.Range("B2"), Order2:=xlAscending, _
Header:=xlYes

ElseIf Not Intersect(Target, Me.Range("A2:A1000")) Is Nothing Then

If Target.Value = "" Then

Me.Cells(Target.row, "F").Value = ""
Else

Me.Cells(Target.row, "F").Value = Format(Now, "dd/mm/yy")
End If
End If
End Sub

coliervile
03-14-2008, 04:34 PM
I'm using this code, but nothig displays in column "F". Ther is data in column "A"- "A2" and there's no date in column "F"- "F2". Any ideas???

[VBA]Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range

For Each Cell In Target

With Cell

If .Column = Me.Range("A:A").Column Then

Me.Cells(.Row, "B").Value = Format("mmm-dd-yyyy hh:mm:ss")
Me.Cells(.Row, "D").NumberFormat = "mmm-dd-yyyy"
Me.Cells(.Row, "E").NumberFormat = "mmm-dd-yyyy"
End If
End With
Next Cell

If Not Intersect(Target, Me.Range("B:B", "E:E")) Is Nothing Then

Me.Columns("A:F").Sort Key1:=Me.Range("B2"), Order1:=xlAscending, _
Header:=xlYes

ElseIf Not Intersect(Target, Me.Range("A2:A1000")) Is Nothing Then

If Target.Value = "" Then

Me.Cells(Target.Row, "F").Value = ""
Else

Me.Cells(Target.Row, "F").Value = Format(Now, "dd/mm/yy")
End If
End If
End Sub[VBA]