Printable View
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,
Code: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
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
I cannot say because I don't know what you mean by "my listviw"Quote:
Originally Posted by djemy1975
In the code I posted, remove or comment-out the lines that set the background (interior) color.Quote:
Originally Posted by 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 screenshotAttachment 23228
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
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,
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
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
I think my code would be something like that:Quote:
Originally Posted by Paul_Hossler
This function should be called from search button "Private Sub CommandButton1_Click()" in my userform.Code: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
Is it possible to adapt this code
Attachment 23237
In case some of the characters did not showCode: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
Attachment 23238
Quote:
Originally Posted by Paul_Hossler
Thank you for your reply.It is fantastic Mr Paul.
Highly appreciated,