PDA

View Full Version : [SOLVED] entries in column A



Omer
05-05-2015, 06:20 PM
I am getting error, can you please help


Sub Button()
Dim C As Range, i As Long
Dim Target As Excel.Range
If Not Intersect(Target, Me.[A:A]) Is Nothing Then
Application.EnableEvents = False
For Each C In Target
If C.Column = 1 And C.Value > "" Then
If WorksheetFunction.CountIf(Me.[A:A], C.Value) > 1 Then
i = C.Interior.ColorIndex
f = C.Font.ColorIndex
C.Interior.ColorIndex = 3 ' Red
C.Font.ColorIndex = 6 ' Yellow
C.Select
MsgBox "Duplicate Entry !", vbCritical, "Error"
C.Interior.ColorIndex = i
C.Font.ColorIndex = f
End If
End If
Next
Application.EnableEvents = True
End If
End Sub

Paul_Hossler
05-05-2015, 07:19 PM
Are you sure that you don't want to put that in a Worksheet event handler?

13303

Omer
05-05-2015, 09:47 PM
I need to use it in module1 then call it from button

Paul_Hossler
05-06-2015, 05:06 AM
You'd need to rewrite it to (probably) use Selection (the WS cells) instead of Target


Something like this maybe



Option Explicit


Sub Button()
Dim C As Range, i As Long, f As Long

If Not TypeOf Selection Is Range Then Exit Sub

Application.EnableEvents = False

For Each C In Intersect(Selection.Parent.UsedRange, Selection.Columns(1)).Cells
With C
If .Column = 1 And Len(.Value) > 0 Then
If WorksheetFunction.CountIf(Selection.Parent.Columns(1), .Value) > 1 Then
i = .Interior.ColorIndex
f = .Font.ColorIndex
.Interior.ColorIndex = 3 ' Red
.Font.ColorIndex = 6 ' Yellow
.Select
MsgBox "Duplicate Entry !", vbCritical, "Error"
.Interior.ColorIndex = i
.Font.ColorIndex = f
End If
End If
End With
Next
Application.EnableEvents = True
End Sub

Omer
05-07-2015, 10:38 AM
Thank you Thank you that worked.