I have codes that need to be tweaked to achieve the desired outcome. The two (2) codes have Private Sub Worksheet_Change(ByVal Target As Range), which is resulting in error. Would like to combine the 2 codes into one and still have the desired result.
“Credit to Linda at Contextures for the first code; credit to Debraj Roy for the second code.” Sample Excel workbook attached for convenience.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
If Target.Column = 11 Then
If Target.Value = "" Then GoTo exitHandler
If Target.Offset(0, 1).Value = "" Then
Target.Offset(0, 1).Value = Target.Value
Else
Target.Offset(0, 1).Value = _
Target.Offset(0, 1).Value _
& ", " & Target.Value
End If
End If
End If
ExitHandler:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("j:k"), Target) Is Nothing And Target.Row >= 6 Then
If ((Range("j" & Target.Row) <> "") * (Range("K" & Target.Row) <> "")) Then
Range("l" & Target.Row).Formula = _
"=I" & Target.Row & "&"" ""&J" & Target.Row & " &"" ""&K" & Target.Row & "&"" - ""&E" & Target.Row
Else
Range("l" & Target.Row) = ""
End If
End If
End Sub
Bookmarks