two macros one sheet not working

[jexcel12345]

Hi,

trying to combine these two codes to work simultaneously. can anyone help as the second macro works as opposed to both.

First Macro:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 36 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Second Macro:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 20140318
Static xRow
Static xColumn
If xColumn <> "" Then
With Columns(xColumn).Interior
.ColorIndex = xlNone
End With
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With Columns(pColumn).Interior
.ColorIndex = 44
.Pattern = xlSolid
End With
With Rows(pRow).Interior
.ColorIndex = 44
.Pattern = xlSolid
End With

End Sub

thanks peoples.

 

[JLGWhiz]

You should copy your current code to a text file (possibly Notepad) in case this code is not what you want. You cannot have two of the same type event codes in the same worksheet code module, so the old code has to be removed from the module or commented out for the new code to function properly.

Private Sub Worksheet_Change(ByVal Target As Range)
Static xRow
Static xColumn
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = False
On Error GoTo Exitsub
    If Target.Column = 36 Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
        ElseIf Target.Value = "" Then
            GoTo Exitsub
        Else
            Newvalue = Target.Value
            Application.Undo
            Oldvalue = Target.Value
            If Oldvalue = "" Then
                Target.Value = Newvalue
            ElseIf InStr(1, Oldvalue, Newvalue) = 0 Then
                Target.Value = Oldvalue & ", " & Newvalue
            Else
                Target.Value = Oldvalue
            End If
        End If
    End If
    If xColumn <> "" Then
        Columns(xColumn).Interior.ColorIndex = xlNone
        Rows(xRow).Interior.ColorIndex = xlNone
    End If
xRow = Target.Row
xColumn = Target.Column
    With Columns(xColumn).Interior
        .ColorIndex = 44
        .Pattern = xlSolid
    End With
    With Rows(xRow).Interior
        .ColorIndex = 44
        .Pattern = xlSolid
    End With
Exitsub:
Application.EnableEvents = True
End Sub

 

[jexcel12345]

hi thanks for your reply, the first code allows the target column to allow multiple entries from the validation drop down list, the second code highlights the current row and column upon cell selection. i'd like both of them to work but it doesn't with that code either.

 

[JLGWhiz]

hi thanks for your reply, the first code allows the target column to allow multiple entries from the validation drop down list, the second code highlights the current row and column upon cell selection. i'd like both of them to work but it doesn't with that code either.

I cannot do anything with the symptoms you describe. You need to define the problem a little better than "It don't work".

 

[jexcel12345]

that code only seems to select a specific column and highlights the column, which is what the second code i provided is supposed to do upon selecting any cell. If you could kindly run the codes seperately to see what they do, for the first code if you create a drop down list on column 36 or any column and change the target column number you will see what i am referring to.

i cant get both of them to work at the same time on the same sheet

 

[Norie]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
    
    On Error GoTo Exitsub
    
    If Target.Column = 36 Then
        Application.EnableEvents = False
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then Exit Sub
        If Target.Value = "" Then Exit Sub
        
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
        Else
            If InStr(1, Oldvalue, Newvalue) = 0 Then
                Target.Value = Oldvalue & ", " & Newvalue
            
            Else
                Target.Value = Oldvalue
            End If
        End If
    End If
Exitsub:
    Application.EnableEvents = True
    
End Sub


Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static xRow
Static xColumn
    If xColumn <> "" Then
        With Columns(xColumn).Interior
            .ColorIndex = xlNone
        End With
        With Rows(xRow).Interior
            .ColorIndex = xlNone
        End With
    End If
    
    pRow = Selection.Row
    pColumn = Selection.Column
    
    xRow = pRow
    xColumn = pColumn
    
    With Columns(pColumn).Interior
        .ColorIndex = 44
        .Pattern = xlSolid
    End With
    
    With Rows(pRow).Interior
        .ColorIndex = 44
        .Pattern = xlSolid
    End With

End Sub

 

[JLGWhiz]

that code only seems to select a specific column and highlights the column, which is what the second code i provided is supposed to do upon selecting any cell. If you could kindly run the codes seperately to see what they do, for the first code if you create a drop down list on column 36 or any column and change the target column number you will see what i am referring to.

i cant get both of them to work at the same time on the same sheet

The first macro in the OP will only execute the value change if the target cell is in column 36 and has a dropdown in that cell.. The second macro colors the target column and target row no matter where the change is made. That is why I put the macro that checks for the dropdown first, so that if there is no drop down, the rows and columns would not be colored. If you expect the macro to change a value in any column other than column 36 it will not, because it specifically designates 36 as the column of choice. Which columns are you making your changes in and which have drop down boxes that initiate the cell value changes? Maybe we can get it straightened out with that info. Better yet, what do you expect to happen when the macro(s) run?