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
            End If
        End If
    Application.EnableEvents = True
    If Len(msg) Then MsgBox msg, , "Invalid entry"
End Sub

enter image description here

  • In For Each r In Intersect(Target, ColumnName1) you iterate over all cells in an intersection range. Maybe it is more clear to iterate over rows in this intersection range and check the integrity for the whole row? – Akina May 6 at 9:53
  • Is there any other way I can write this code – kumar May 6 at 9:59
  • 2
    Try the pattern "########-[A-Za-z][A-Za-z][A-Za-z]*" (8 digits, dash, 3 letters, any amount of any symbols). – Akina May 6 at 10:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.