Check for Duplicates and set flag

[carlysyme]

I have this macro: It all works fine apart from one thing the worksheet function is not returning anything I have tried other worksheet functions and they return 0.

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

[DonkeyOte]

The Application.WorksheetFunction will not evaluate your Range ... ie it will not read:

COUNTIF(RANGE("A1:A10"),A2)

As COUNTIF A1:A10 = A2... in this context you must use a range, eg:

COUNTIF(RANGE("A1:A10"),RANGE("A2"))

This is all based on the assumption that this:

"A" & CStr(StartCount)

is meant to generate the criteria Range....

There are other ways to do what you're trying to do but I believe the above answers your question... if you could clarify a few things we may be able to streamline further, ie:

1.  Is the Change event meant to fire anytime any value on the worksheet changes or only when A6:A20 changes ?
2.  If the StartCount and maxCount are set to 6 & 20 respectively why then is the COUNTIF range 6:999, should not the MaxCount variable be set to 999 also ?
3.  Are you writing back your Text for Duplicates to a Different Sheet or is "Room and Bed" the same sheet on which the Change event code is firing ?

[carlysyme]

Hi

sorrry for duplicate threading everywhere I couldn't find my orginal post.

1. Is the Change event meant to fire anytime any value on the worksheet changes or only when A6:A20 changes ?
The range is for test purposes I will put some kind of data count on that column. i would like the macro to run everytime that column is updated

2. If the StartCount and maxCount are set to 6 & 20 respectively why then is the COUNTIF range 6:999, should not the MaxCount variable be set to 999 also ?
Just for test purposes

3. Are you writing back your Text for Duplicates to a Different Sheet or is "Room and Bed" the same sheet on which the Change event code is firing ?

Room and Bed is the same sheet to which I am writing to.

Thanks for your help

[DonkeyOte]

Perhaps then something along the lines of:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Set rng = Range(Cells(6, "A"), Cells(Rows.Count, "A").End(xlUp))
rng.Interior.ColorIndex = xlNone
Application.EnableEvents = False
With rng.Offset(0, 1)
    .ClearContents
    .FormulaR1C1 = "=IF(COUNTIF(" & rng.Address(1, 1, xlR1C1) & ",RC1)>1,""This is Text"",0)"
    On Error Resume Next
    .SpecialCells(xlCellTypeFormulas, xlTextValues).Offset(0, -1).Interior.ColorIndex = 3
    .SpecialCells(xlCellTypeFormulas, xlNumbers).Clear
    On Error GoTo 0
    .Formula = .Value
End With
ExitPoint:
Application.EnableEvents = True
Exit Sub

Handler:
Resume ExitPoint
End Sub

Note, in the above I clear values from B before checking results of COUNTIF etc... this was not in your original code but I suspect is a requirement.