Macro validation and conditional check

Question

i have below excel where i want to do data validation for ColumnName1 ColumnName2 ColumnName3 & ColumnName4 and as well check the condition if ColumnName6 = ColumnName7 && ColumnName5 = OK then ColumnName8 is NOC else CON using Macros

For condition check i am using below formula unable to get how to use third condition

if(ColumnName6 = ColumnName7 ,NOC,CON)

For Validation check i am written below macros but its no working as expected

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range, msg As String
    If Intersect(Target, ColumnName1) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each r In Intersect(Target, ColumnName1)
        If r.Value <> "" Then
            If Not r.Value Like "########-[A-Za-z][A-Za-z][A-Za-z]" Then
                MsgBox "Invalid Entry", vbCritical, r.Value
                r.ClearContents
            End If
        End If
    Next
    Application.EnableEvents = True
    If Len(msg) Then MsgBox msg, , "Invalid entry"
End Sub