Worksheet Change Event Non Contiguous Range

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    Dim RngA    As String
    Dim RngC    As String
    LastRow = 5
    RngA = "A1:A" & LastRow
    RngC = "C1:C" & LastRow
    
    If Not Intersect(Target, Range(RngA, RngC)) Is Nothing Then
        MsgBox "Hello"
    Else
        MsgBox "bye"
    End If
End Sub

Does this do what you want?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim targetArea As Range
    Dim intersectCount As Long
    Dim testRange As Range
    Dim testArea As Range
    Dim lastRow As Long
    
    lastRow = 5
    Set testRange = Union(Range("A" & lastRow), Range("C" & lastRow))
    For Each targetArea In Target.Areas
        For Each testArea In testRange.Areas
            If Not Intersect(targetArea, testArea) Is Nothing Then
                intersectCount = intersectCount + 1
                Exit For
            End If
        Next
    Next
    If testRange.Areas.Count = Target.Areas.Count Then
        If intersectCount = testRange.Areas.Count Then
            MsgBox "Matched"
        Else
            MsgBox "No Match"
        End If
    Else
        MsgBox "No Match"
    End If
    
End Sub

You need to provide more useful feed back.

For me, if I select A5 and C5 and enter values in both cells at once using CTRL+ENTER it works.

If I have both selected but only enter value into 1 cell, using Enter, then it does not work.
If I select only 1 of the cells and enter it does not work.
If I select more cells it does not work.

Please explain the circumstances under which it should work

Maybe it is just a very simple case, like :

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lastrow As Long
  lastrow = 5

  If Not Intersect(Target, Union(Range("A" & lastrow), Range("C" & lastrow))) Is Nothing Then
     MsgBox "Hello"
  End If
End Sub

To me, non contiguous means a reference to 2 or more cells.

You simply mean 2 non adjacent cells