Listview specific rows conditional formatting

[djemy1975]

Dear friends,

Is it possible to format rows in listview and sheet according to status "Etat" (column 47 in my 3 sheets) value: I need a conditional formatting like this:
1- When Column 47 value is "clôturé" ,I want the whole row in my listview becomes red.
2- When Column 47 value is "en cours" ,I want the whole row in my listview becomes green.

Is this approach can be done?

Waiting for your reply.Thanks in advance,

[rlv]

Sub SetSpecialFormatCondition()
    Dim WS As Worksheet
    Dim RangeOfCells As Range
    Dim S As String, FormulaStrRed As String, FormulaStrGreen As String
    Dim FC As FormatCondition


    For Each WS In ThisWorkbook.Worksheets
        S = Trim(WS.Range("AU1").Value)
        If S = "Etat" Then
            FormulaStrRed = "=$AU2=" & """" & "clôturé" & """"    '=$AU2="clôturé"
            FormulaStrGreen = "=$AU2=" & """" & "en cours" & """"    '=$AU2="en cours"


            Set RangeOfCells = WS.Range("A2:BH" & WS.Range("AU" & WS.Rows.Count).End(xlUp).Row)
            With RangeOfCells
                For Each FC In .FormatConditions
                    If FC.Type = xlExpression And (FC.Formula1 = FormulaStrRed Or FC.Formula1 = FormulaStrGreen) Then
                        FC.Delete
                    End If
                Next FC
                
                Set FC = .FormatConditions.Add(Type:=xlExpression, Formula1:=FormulaStrGreen)
                With FC
                    .SetFirstPriority
                    With .Font
                        .ThemeColor = xlThemeColorDark1
                        .ThemeFont = xlThemeFontNone
                    End With
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 5287936
                    End With
                    .StopIfTrue = True
                End With
                
                Set FC = .FormatConditions.Add(Type:=xlExpression, Formula1:=FormulaStrRed)
                With FC
                    .SetFirstPriority
                    With .Font
                        .ThemeColor = xlThemeColorDark1
                        .ThemeFont = xlThemeFontNone
                    End With
                    With .Interior
                        .PatternColorIndex = xlAutomatic
                        .Color = 255
                    End With
                    .StopIfTrue = True
                End With
            End With
        End If
    Next WS
End Sub

[djemy1975]

Dear rlv,
Thank you for your prompt reply.It is working for all sheets ,but I want it to work on my listviw.Is it possible?.

As for sheets ,I want font colour only to be changed not interior.

Waiting for your kind reply

[rlv]

Dear rlv,
Thank you for your prompt reply.It is working for all sheets ,but I want it to work on my listviw.Is it possible?.

I cannot say because I don't know what you mean by "my listviw"

As for sheets ,I want font colour only to be changed not interior.

In the code I posted, remove or comment-out the lines that set the background (interior) color.

[djemy1975]

I have a userform used to filter records in listview1 .I want to apply conditional formatting on that listview (Make the row in red or green according to value in"Etat" column ("clôturé" or "en cours") .Herewith a screenshotlistview.jpg

[Paul_Hossler]

Googling "excel vba listview row color" finds a number of posts that you can use

Including one here on VBAexpress --

http://www.vbaexpress.com/kb/getarticle.php?kb_id=916

[djemy1975]

Thank you for reply .I tried to use it before and spent much time ,but I could not adapt it as my listview pulls data from three sheets.Could you help me adapt it.
Best regards,

[Paul_Hossler]

Multiple sheet should not make a difference

As you Add the data to your ListView check and change color as needed

If you make a very simple test workbook, we can look at that

[djemy1975]

ok I will attach a simple workbook while trying to figure out a solution to the workbook I am working on.I will be back tonight when logged in with my pc.
thanks

[djemy1975]

Multiple sheet should not make a difference

As you Add the data to your ListView check and change color as needed

If you make a very simple test workbook, we can look at that

I think my code would be something like that:

Private Sub CondFormat()'Conditional formatting
On Error Resume Next
For i = 0 To ListView1.ListItems.Count - 1
     
 
       
     If ListView1.ListItems(i).ListSubItems(48).Text = "clôturé" Then
        ListView1.ListItems(i).ListSubItems(48).ForeColor = vbRed
     End If
     If ListView1.ListItems(i).ListSubItems(48).Text = "en cours" Then
        ListView1.ListItems(i).ListSubItems(48).ForeColor = vbGreen
     End If
     If ListView1.ListItems(i).ListSubItems(48).Text = "a clôturer" Then
        ListView1.ListItems(i).ListSubItems(48).ForeColor = vbBlue
       
     End If
                   
    Next i
End Sub

This function should be called from search button "Private Sub CommandButton1_Click()" in my userform.
Is it possible to adapt this code

[Paul_Hossler]

Capture.JPG

Private Sub CondFormat()
    Dim i As Long, i1 As Long
    For i = 1 To ListView1.ListItems.Count
        Select Case Trim(ListView1.ListItems(i).ListSubItems(47).Text)
            Case "cl?tur?"
                For i1 = 1 To ListView1.ListItems(i).ListSubItems.Count
                    ListView1.ListItems(i).ListSubItems(i1).ForeColor = vbRed
                Next i1
            Case "en cours"
                For i1 = 1 To ListView1.ListItems(i).ListSubItems.Count
                    ListView1.ListItems(i).ListSubItems(i1).ForeColor = vbGreen
                Next i1
            Case "a cl?turer"
                For i1 = 1 To ListView1.ListItems(i).ListSubItems.Count
                    ListView1.ListItems(i).ListSubItems(i1).ForeColor = vbBlue
                Next i1
        End Select
    Next i
End Sub

In case some of the characters did not show

Capture1.JPG

[djemy1975]

Capture.JPG

Private Sub CondFormat()
    Dim i As Long, i1 As Long
    For i = 1 To ListView1.ListItems.Count
        Select Case Trim(ListView1.ListItems(i).ListSubItems(47).Text)
            Case "cl?tur?"
                For i1 = 1 To ListView1.ListItems(i).ListSubItems.Count
                    ListView1.ListItems(i).ListSubItems(i1).ForeColor = vbRed
                Next i1
            Case "en cours"
                For i1 = 1 To ListView1.ListItems(i).ListSubItems.Count
                    ListView1.ListItems(i).ListSubItems(i1).ForeColor = vbGreen
                Next i1
            Case "a cl?turer"
                For i1 = 1 To ListView1.ListItems(i).ListSubItems.Count
                    ListView1.ListItems(i).ListSubItems(i1).ForeColor = vbBlue
                Next i1
        End Select
    Next i
End Sub

In case some of the characters did not show

Capture1.JPG

Thank you for your reply.It is fantastic Mr Paul.

Highly appreciated,