Colouring the ListView item if it is in date range

[lapot]

Dear all

I have an excel user form whish has a ListView , all the Listview data comes from excel sheet. What I m trying to do is colour code the list view item.
I have Note column which is SubItems(17) and contains notes
I have "Tarih" Column which is on Sheets("DATA") Column A and contains Dates

If Note is not blank and Tarih = Today then ListView item colour is Magenta
If Note is not blank and Tarih not equal to Today and Tarih-Today difference greater than 30 then ListView item colour is Red
If Note is not blank and Tarih not equal to Today and Tarih-Today difference less than 30 then ListView item colour is Green
If Note is Blank then ListView item colour Blue

but below code doesn't work, can you please let me know what I am missing. I cant put sample data as some of the info comes from the server.

Private Sub FormatListView1()
Dim Item As ListItem
Dim Counter As Long
Dim Note As String
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").Cells(1 + Counter, 3)


With Me.ListView1

If Note <> "" And Tarih = Today Then
For n = 1 To 17
.ListItems.Item(Counter).ForeColor = vbMagenta
.ListItems.Item(Counter).ListSubItems(n).ForeColor = vbMagenta
Next n
Else

If Note <> "" And Today <> Tarih And Today - Tarih > 30 Then
For n = 1 To 17
.ListItems.Item(Counter).ForeColor = vbRed
.ListItems.Item(Counter).ListSubItems(n).ForeColor = vbRed
Next n
Else

If Note <> "" And Today <> Tarih And Today - Tarih < 30 Then
For n = 1 To 17
.ListItems.Item(Counter).ForeColor = vbGreen
.ListItems.Item(Counter).ListSubItems(n).ForeColor = vbGreen
Next n

Else
If Note = "" 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

Kind regards

 

[Domenic]

Having taken a quick look... use Date instead of Today...

Hope this helps!

 

[lapot]

I did that but still not working. Any other sugestion please?

 

[lapot]

can any one help please ?

 

[Domenic]

I haven't tested it, but try the following...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] FormatListView1()

    [COLOR=darkblue]Dim[/COLOR] Item [COLOR=darkblue]As[/COLOR] ListItem
    [COLOR=darkblue]Dim[/COLOR] Counter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Note [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Tarih [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Date[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Color [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] n [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=green]' Set the variable to the ListItem.[/COLOR]
    [COLOR=darkblue]For[/COLOR] Counter = 1 [COLOR=darkblue]To[/COLOR] Me.ListView1.ListItems.Count
    
        [COLOR=darkblue]Set[/COLOR] Item = Me.ListView1.ListItems.Item(Counter)
        
        [COLOR=green]' Set the variable to the Freight[/COLOR]
        Note = Item.SubItems(17)
        
        Tarih = Sheets("DATA").Cells(1 + Counter, 3)
        
        [COLOR=darkblue]With[/COLOR] Me.ListView1
        
            [COLOR=darkblue]If[/COLOR] Note <> "" [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]If[/COLOR] Tarih = [COLOR=darkblue]Date[/COLOR] [COLOR=darkblue]Then[/COLOR]
                    Color = vbMagenta
                [COLOR=darkblue]ElseIf[/COLOR] [COLOR=darkblue]Date[/COLOR] - Tarih > 30 [COLOR=darkblue]Then[/COLOR]
                    Color = vbRed
                [COLOR=darkblue]ElseIf[/COLOR] [COLOR=darkblue]Date[/COLOR] - Tarih < 30 [COLOR=darkblue]Then[/COLOR]
                    Color = vbGreen
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Else[/COLOR]
                Color = vbBlue
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            
            Item.ForeColor = Color
            
            [COLOR=darkblue]For[/COLOR] n = 1 [COLOR=darkblue]To[/COLOR] 17
                Item.ListSubItems(n).ForeColor = Color
            [COLOR=darkblue]Next[/COLOR] n
        
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]Next[/COLOR] Counter
    
    Me.ListView1.Refresh
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[lapot]

this worked thank you very much