Shared excel not picking up colour change

[Puggwash63]

I have an excel sheet that has a program to colour the Tab when either the Rows are showing Green or Red

If the spreadsheet is not Shared, the formula works, but if the spreadsheet is Shared, it will not pick up the colour when any of the cells in column L are either chosen as Yes (should turn Green if there isnt any Red cells) or No (Should turn Red, even if there are Green cells in Column L showing)

Hope this makes sense and you can help

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range: Set rng = Range([L8], Cells(Rows.Count, "L").End(xlUp))
If Not Intersect(Target, rng) Is Nothing Then
If WorksheetFunction.CountIf(rng, "No") <> 0 Then
ActiveSheet.Tab.Color = vbRed
ElseIf WorksheetFunction.CountIf(rng, "") = rng.Cells.Count Then
ActiveSheet.Tab.ColorIndex = xlColorIndexNone
ElseIf WorksheetFunction.CountIf(rng, "Yes") + WorksheetFunction.CountIf(rng, "") = rng.Cells.Count Then
ActiveSheet.Tab.Color = vbGreen
Else
ActiveSheet.Tab.ColorIndex = xlColorIndexNone
End If
End If
End Sub

 

[Puggwash63]

Does anyone have any answers that would help me with this issue