Dashboard Reporting

[highlander2536]

I have created an excel dashboard that contains two separate traffic light charts. Each traffic light collects its data from a different cell. When only 1 traffic light is showing and the VB code is for 1 light it works. When I add the second light and code I get a "Compile Error: Ambiguous name detected: Worksheet_Change"

The code is linked to the worksheet as an excel object.

Private Sub Worksheet_Change(ByVal Target As Range)
       
    If Not Intersect(Target, Range("V1")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            If Target.Value < 55 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbRed
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 55 And Target.Value < 60 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbYellow
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 60 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbGreen
            End If
        End If
    End If
    End If
    End If
End Sub
-----------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
       
    If Not Intersect(Target, Range("V24")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            If Target.Value < 55 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbRed
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 55 And Target.Value < 60 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbYellow
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 60 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbGreen
            End If
        End If
    End If
    End If
    End If
End Sub

Is there any way to run both codes without the error?

Thanks

 

[VoG]

You'll need to combine them like this

Private Sub Worksheet_Change(ByVal Target As Range)
       
    If Not Intersect(Target, Range("V1")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            If Target.Value < 55 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbRed
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 55 And Target.Value < 60 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbYellow
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 60 Then
                ActiveSheet.Shapes("Oval 10").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 11").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 12").Fill.ForeColor.RGB = vbGreen
            End If
        End If
    End If
    End If
       
    ElseIf Not Intersect(Target, Range("V24")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            If Target.Value < 55 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbRed
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 55 And Target.Value < 60 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbYellow
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbBlack
            Else
            If Target.Value >= 60 Then
                ActiveSheet.Shapes("Oval 8").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 13").Fill.ForeColor.RGB = vbBlack
                ActiveSheet.Shapes("Oval 14").Fill.ForeColor.RGB = vbGreen
            End If
        End If
    End If
    End If
    End If
End Sub

 

[JackDanIce]

No, you need to combine them into a single procedure of Private Sub Worksheet_Change, I think because Worksheet_Change is specifically reserved for identifying and triggering events relating to a change in the sheet you're working on, the code can't differentiate between two identical names that trigger the same event

 

[highlander2536]

Thanks Peter, thats worked perfectly.