two macros one sheet not working

jexcel12345

New Member
Joined
Feb 20, 2018
Messages
7
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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".
 
Last edited:
Upvote 0
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
 
Upvote 0
Try this.
Code:
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
 
Upvote 0
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?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top