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 screenshot23228

[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]

23237



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

23238

[djemy1975]

23237



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

23238


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

Highly appreciated,