Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$7" Or Target.Address = "$N$40" Then
        MsgBox "Mexeu na celula"
    End If
End Sub

Tem o jeito mais fácil, acima, mas que não funciona se vc selecionar diversas células e dentre elas, a C7 ou a N40.
Digamos, selecione o intervalo de b5 a e10 e digite "texto" e tecle Ctrl+Enter. Assim vc escreve em todas as células ao mesmo tempo, e o endereço do target (target.address) não é só $C$7, pois vc tem mais coisas mudando, o endereço neste exemplo é $B$5:$E$10. Assim seu código não funcionará, pois a célula C7 está no meio, e seu código não vai identificá-la e a msgbox não aparecerá.

Eu diria que o ideal seria o código abaixo:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Planilha    As Excel.Worksheet
Dim Celula      As Excel.Range

    Set Planilha = Target.Parent
    With Application
        Set Celula = .Intersect(Target, Planilha.Range("$C$7"))
        If Celula Is Nothing Then
            Set Celula = .Intersect(Target, Planilha.Range("$N$40"))
        End If
    End With
    
    If Not Celula Is Nothing Then
        MsgBox "Mexeu na celula"
        Exit Sub
    End If

    Set Celula = Nothing
    Set Planilha = Nothing
End Sub

Obrigado! Fiz uma pesquisa, adaptei e cheguei ao mesmo resultado.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$7" Or Target.Address = "$N$40" Then
        MsgBox "Mexeu na celula"
    End If
End Sub

Vc vai ter problemas. Lembre disso.