Set flag if column has dup

[carlysyme]

I have this macro:

Private Sub Worksheet_Change(ByVal Target As Range)


Dim MaxCount As Integer
Dim StartCount As Integer
Dim CountRange As String
Dim LChangedValue As String
On Error GoTo ErrorHandler
Application.ScreenUpdating = False

MaxCount = 20
StartCount = 6

'Clear all flags
CountRange = "A6:A" & MaxCount
Range(CountRange).Interior.ColorIndex = xlNone


While StartCount < MaxCount
LChangedValue = "A" & CStr(StartCount)
test = Application.WorksheetFunction.CountIf(Range("A6:A999"), "A" & CStr(StartCount))
If test > 1 Then
Range(LChangedValue).Interior.ColorIndex = 3
Worksheets("Room and Bed").Range("B" & CStr(StartCount)).Value = "This is text"
End If
StartCount = StartCount + 1
Wend

ErrorHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

I think it should do what I need it to do but at present worksheet function is not assigning any value. Is there something I am missing or something I may have done which will make this not work.

Thanks

[DonkeyOte]

Duplicate Thread

http://www.excelforum.com/excel-work...-set-flag.html