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