Colouring the listview item by date rage

[Ramazan Topal]

Private Sub FormatListView1()
Dim Item As ListItem
Dim Counter As Long
Dim Tarih As Date

' Set the variable to the ListItem.
For Counter = 1 To Me.ListView1.ListItems.Count
Set Item = Me.ListView1.ListItems.Item(Counter)
' Set the variable to the Freight

Note = Item.SubItems(17)
Tarih = Sheets("DATA").Range("C2:C2000")

With Me.ListView1
If Tarih = Today Then
For n = 1 To 17
.ListItems.Item(Counter).ForeColor = vbYellow
.ListItems.Item(Counter).ListSubItems(n).ForeColor = vbYellow
Next n
Else
If Date - Tarih > 30 Then
For n = 1 To 17
.ListItems.Item(Counter).ForeColor = vbRed
.ListItems.Item(Counter).ListSubItems(n).ForeColor = vbRed
Next n
Else

If Date - Tarih < 30 Then
For n = 1 To 17
.ListItems.Item(Counter).ForeColor = vbGreen
.ListItems.Item(Counter).ListSubItems(n).ForeColor = vbGreen
Next n
Else
If Tarih = "" Then
For n = 1 To 17
.ListItems.Item(Counter).ForeColor = vbBlue
.ListItems.Item(Counter).ListSubItems(n).ForeColor = vbBlue
Next n

End If
End If
End If
End If
End With
Next Counter
Me.ListView1.Refresh
End Sub

Dear all

Above code to colour listview items in date range. I have an excel form and it has a Sheet called DATA. On the DATA sheet Column C, I have various dates and I am comparing these dates with today's date and if they are in different range I like them colour differently on the listview. The code is giving an error on this line

Tarih = Sheets("DATA").Range("C2:C2000")

Do you know why?

Kind regards

Edit : moved to appropriate VBA forum !

 

[Hui]

You have declared Tarih a date in the definitions at the top of your code
Then you are trying to load it with a range?

Can you post your file ?

 

[Ramazan Topal]

My file is so big. It has 5k row. What I m trying to achieve is in DATA sheet Column C has 2000 various dates. In my code I want to compare each date on Column C to today's date and according to difference change listview item colour on the user form. Does this make sense?

Thank you for your help in advance

 

[Hui]

Can you save it as an Excel Binary file *.xlsb

Then try again

Or simply delete 4900 rows and save as a backup file

 

[Ramazan Topal]

Sorry I cant attach the form as it has connected to the Data base , it wont open , Screen shots enough?

 

[vletm]

@Ramazan Topal test this

'  no work, You need just one date, which match with Counter
'  Tarih = Sheets("DATA").Range("C2:C2000")
'  next works, but You gotta which number match, now I test with number 1
Tarih = Sheets("DATA").Cells(1 + Counter, 3)

 

[Ramazan Topal]

This has worked perfectly. Thank you so much , you are a star vletm