VBA - If cell in range is greater than = MsgBox

Try this
It checks (using CountIf) to see if any values in columnC exceed 2000 and then identifies which rows, and delivers a message with details

Sub msg()

Dim msg As String, lastrow As Long, c As Long

    lastrow = Sheets("Sheet1").Range("A1048576").End(xlUp).Row
    c = Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("C2:C" & lastrow), ">2000")
    
    If c > 0 Then
        For r = 2 To lastrow
            If sheets("Sheet1").Cells(r, 3).Value > 2000 Then msg = msg & r & ","
        Next r
    End If

    If Not msg = "" Then MsgBox "The following " & c & "rows contain values over 2000" & vbCr & msg

End Sub

You could build your conditional formatting into the same loop

If you want the message delivered without running the macro manually, then an event macro is required instead, triggered when values in the sheet change. (Is this what you want?)