Help with existing pop up code

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng As Range, Dn As Range
Dim num As Long
If Target.Count = 1 And Not Intersect(Target, Rows("3:3")) Is Nothing Then
    Cancel = True
     Set Rng = Range(Cells(4, Target.Column), Cells(Rows.Count, Target.Column).End(xlUp))
       num = Application.CountIf(Rng, "HP")
         MsgBox Target & " Has " & num & " HP's"
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, Dn As Range
Dim nDn As Range
Dim Msg As String
Dim nMsg As String
Dim nRng As Range
Dim Depot As String
Dim Fd As Boolean
Dim nFd As Boolean
Set Rng = Range(Range("C3"), Cells(3, Columns.Count).End(xlToLeft))
Set Rng = Rng.SpecialCells(xlCellTypeConstants)
    For Each Dn In Rng.Areas
        If nRng Is Nothing Then
            Set nRng = Dn
        Else
            Set nRng = Union(nRng, Dn.Offset(, 2).Resize(, Dn.Count - 2))
        End If
Next Dn
For Each nDn In nRng.Areas
   If Not Intersect(Target, nDn.Offset(1).Resize(Rows.Count - 3)) Is Nothing Then Fd = True
Next nDn
If Fd Then
    For Each nDn In nRng.Areas
       Depot = nDn.Offset(-2).Resize(, 1)
        Set nDn = nDn.Offset(Target.Row - 3)
             For Each Dn In nDn
                If Target = "HP" And Dn <> "" And Not Dn.Address = Target.Address Then
                    Msg = Msg & Cells(3, Dn.Column) & Chr(10)
                 nFd = True
                End If
            Next Dn
                nMsg = nMsg & "Depot " & """" & Depot & """" & Chr(10) & Msg & Chr(10)
                Msg = ""
    Next nDn
End If
If nFd And Target = "HP" Then
    MsgBox "The following Staff are off today " & Chr(10) & Chr(10) & nMsg & Chr(10) & "Press ""OK"" to continue !!"
End If
End Sub

Private Sub worksheet_beforerightclick(ByVal Target As Range, cancel As Boolean)
If Target.Row <> 3 Then Exit Sub
cancel = True
MsgBox Target.Value & " Has " & Application.CountIf(Target.EntireColumn, "HP") & " Days Booked So Far"
End Sub
 
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim intclr As Integer
 
If Target.Count > 1 Then Exit Sub
 
If Not Intersect(Target, Range("A1:CB316")) Is Nothing Then
 
Select Case Target
Case "HP": intclr = 40
Case "MP": intclr = 36
Case "SU": intclr = 35
Case "BH": intclr = 37
Case "HU": intclr = 43
Case "RD": intclr = 42
Case "": intclr = xlNone
Case Else
End Select
 
Target.Interior.ColorIndex = intclr
 
End If
 
End Sub

'From Change Event code
If nFd And Target = "HP" Then
    MsgBox "The following Staff are off today " & Chr(10) & Chr(10) & nMsg & Chr(10) & "Press ""OK"" to continue !!"
End If
'Add your existing code here----
Dim intclr As Integer
 
If Target.Count > 1 Then Exit Sub
 
If Not Intersect(Target, Range("A1:CB316")) Is Nothing Then
 
Select Case Target
Case "HP": intclr = 40
Case "MP": intclr = 36
Case "SU": intclr = 35
Case "BH": intclr = 37
Case "HU": intclr = 43
Case "RD": intclr = 42
Case "": intclr = xlNone
Case Else
End Select
 
Target.Interior.ColorIndex = intclr
End If
End Sub