Data validation not working

[waheed254]

Dear All,
I am facing problem regarding Data Validation that when i Type data in data validation activated cell it shows error when i type Data which is not available in my list but when i copy Paste/Paste Special in Same cell it do not show error it just Paste value if it is in my list or Not. I think an error must be displayed while copy pasting data if it is not in my specified List if Source Data...


Can anyone Help...

 

[Peter_SSs]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ChangedC As Range, ChangedD As Range, c As Range, Found As Range
  Dim DVListC As Range, DVLIstD As Range
  Dim sInvalid As String
  Dim val As Variant
  
  Set DVListC = Sheets("DLR").Range("D4:D3000")
  Set DVLIstD = Sheets("DLR").Range("E4:E3000")
  
  Set ChangedC = Intersect(Target, Range("C4:C5000"))
  Set ChangedD = Intersect(Target, Range("D4:D5000"))
  
  Application.EnableEvents = False
  If Not ChangedC Is Nothing Then
    For Each c In ChangedC
      val = c.Value
      If Len(val) Then
        Set Found = DVListC.Find(What:=val, LookIn:=xlValues, _
            LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Found Is Nothing Then
          sInvalid = sInvalid & vbLf & c.Address(0, 0) & vbTab & val
          c.Interior.ColorIndex = 3
        Else
          c.Interior.ColorIndex = xlNone
        End If
      End If
    Next c
  End If
  
  If Not ChangedD Is Nothing Then
    For Each c In ChangedD
      val = c.Value
      If Len(val) Then
        Set Found = DVLIstD.Find(What:=val, LookIn:=xlValues, _
            LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Found Is Nothing Then
          sInvalid = sInvalid & vbLf & c.Address(0, 0) & vbTab & val
          c.Interior.ColorIndex = 3
        Else
          c.Interior.ColorIndex = xlNone
        End If
      End If
    Next c
  End If
  Application.EnableEvents = True
  
  If Len(sInvalid) Then
    MsgBox "The following entries were invalid and have been highlighted" & sInvalid
  End If
  
End Sub

 

[waheed254]

Fantastic that is Brilliant thank u very much again..... I am not closing this thread if any issue rises i shall Post, else i shall close....

 

[Peter_SSs]

Fantastic that is Brilliant thank u very much again..... I am not closing this thread if any issue rises i shall Post, else i shall close....

There is no option to 'Close' a thread in this forum. We like to leave them open because another member may come along later and offer a much better solution.

A post that a suggestion satisfies your current need (as you have done) is a sufficient conclusion to your thread.