run macro depending on cell value

[bamaisgreat]

I have the code below in my worksheet. I keep getting the ambigous error. The last section of code is what im trouble with. Thanks as always.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    If Not Intersect(Target, Range("I4:I20")) Is Nothing Then
        Application.EnableEvents = False
        For Each cell In Intersect(Target, Range("I4:I20"))
            If Not IsEmpty(cell) Then
                Cells(cell.Row, "S").Value = Now
            Else
                Cells(cell.Row, "S").ClearContents
            End If
        Next cell
        Application.EnableEvents = True
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)




    If Target.Address = "$G$1:$J$1" Then
        Call FINALIZED_BY_QC_job
    ElseIf Target.Address = "$B$9,$B$11" Then
        Call SHOW_LIST
    End If
    End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$V$1" Then Exit Sub
If Target.Value = "Email" Then Call Email8 'macroname sunday


End Sub

 

[Joe4]

You cannot have two "Worksheet_Change" procedures on the same sheet. You will need to work the code of your second one into your first one.

 

[bamaisgreat]

You cannot have two "Worksheet_Change" procedures on the same sheet. You will need to work the code of your second one into your first one.

Ok, i have tried the code below but Im still not having any success. Could you take a look ? Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Target.Address = "$H$1:$K$1" Then
        Call FINALIZED_BY_QC_job
    End If
   
   
    If Target.Address = "$W$4,$W$8" Then
    Call Mail_Workbook_1
    End If
   
   
End Sub

 

[Joe4]

By
If Target.Address = "$H$1:$K$1" Then
do you mean you are checking to see if any cell in the range H1:K1 has been updated?
If so, you need to use the INTERSECT method, like in your original example.

Also, by
If Target.Address = "$W$4,$W$8" Then
do you mean checking to see if only W4 or W8 has been updated?
Then we need to check for that a bit differently, checking each cell address separately.

If my assumptions are correct, changing your code to this should work:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    
    If Not Intersect(Target, Range("H1:K1")) Is Nothing Then
        Call FINALIZED_BY_QC_job
    End If
   
    If (Target.Address = "$W$4") Or (Target.Address = "$W$8") Then
        Call Mail_Workbook_1
    End If

End Sub

 

[bamaisgreat]

Sorry for not clarifying. I am simply holding down ctrl then selecting H1 then K1 to run the macro.

 

[bamaisgreat]

Sorry for not clarifying. I am simply holding down ctrl then selecting $W$4,$W$8 to run the macro.

Typo

 

[Joe4]

If you select any cells in those areas, the macros should run, as it is written since it is a Worksheet_SelectionChange event procedure.
Note that it will run when ANY cells is selected in those ranges.
Do you only want it to run if ALL the cells in H1:K1 are selected, or both W4 and W8 are selected simultaneously?

 

[bamaisgreat]

The H1:K1 work as they should. The one with the W4 and W8 is the new one Im wanting to add to the to the selection change. I would like it were when I hold CTRL and click on W4 and W8 it will run the macro. Thanks for you help.

 

[Joe4]

If you only want it to run when both W4 and W8 are selected simultaneously, change this line:

If (Target.Address = "$W$4") Or (Target.Address = "$W$8") Then

to this:

If (Not Intersect(Target, Range("W4")) Is Nothing) And (Not Intersect(Target, Range("W8")) Is Nothing) Then

 

[bamaisgreat]

ok seems to be working good now. I did however add a unprotect and save before running the rest of the code and it has seemed to mess up. How can i work with this ?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)


[COLOR=#ff0000]     ActiveSheet.Unprotect[/COLOR]
[COLOR=#ff0000]     ActiveWorkbook.Save[/COLOR]
    
    If Not Intersect(Target, Range("H1:K1")) Is Nothing Then
        Call FINALIZED_BY_QC_job
    End If
   
    If (Not Intersect(Target, Range("W4")) Is Nothing) And (Not Intersect(Target, Range("W8")) Is Nothing) Then
        Call Mail_Workbook_1
    End If
    
   [COLOR=#ff0000] ActiveSheet.Protect[/COLOR]


End Sub