Conditional format cells in listview

How easy is it for me to switch to using Flexgrid instead of listview?  Im guessing that is the suggestion - to use one instead of the other?

1.  Remove your listview control
2.  Add a grid control ( flexgrid is an option, as well as GridEx or even a third party grid such as a JanusGrid or APEX True DBGrid )

3.  As far as using the FlexGrid see this
https://www.experts-exchange.com/questions/20243733/ado-and-flexgrid.html?sfQueryTermInfo=1+10+30+flexgrid+recordset+set+vb6

So I now have my msflexgrid showing the information but going back to the original question I cant seem to find anything by searching flexgrid conditional formatting so how do I get the cells in column 5 to be conditionally formatted based on their values?

I figure how to do it but only with standard colours.  Now trying to figure out how to generate non standard colours and then will extend all 6 possibilities

For s = 1 To MSFlexGrid1.Rows - 1
MSFlexGrid1.col = 4
MSFlexGrid1.Row = s
cellval = MSFlexGrid1.Text
Select Case cellval
Case "1"
MSFlexGrid1.CellBackColor = vbBlue
MSFlexGrid1.ForeColor = vbBlue
Case "2"
MSFlexGrid1.CellBackColor = vbRed
MSFlexGrid1.ForeColor = vbRed
End Select
Next s

Select allOpen in new window

If you need Non Standard Colors and you know the Hex Format you could convert that to create the OLE_COLOR number that you need...
vbRed = 255
Hex = "FF0000"
Depends on how you wish to decide the color...
1.  Create a table in your database of Colours you like to use with their Hex or OLE_COLOR values...
2.  Create an array of the same...


With the Janus Grid...
   you would use a RowFormat condition where you either define Grid Styles at runtime or design time.
and when the grid refreshes it automatically sets your colours based on your conditions as the grid is painted.

Worth the $$$ if you are going to use it often enough.

I have now figured out some code which works great by specifying hex colour prefixed with #H the only odd thing is that all the oranges are actually displayed as blues

are your hex codes all 6 chars?

Red and blue are opposite..
red = #FF0000
blue = #0000FF

when converting to hex from OLE you must switch pairs 1 and 3

I managed to create working code with your help as shown below.  The code looks for the values in the 4th column and changes the colour of that cell based on the value.  It then goes on to look at a hidden cell 0 and remove duplicated values in the 3 columns to the right by turning the text white.  Although this is the end result it was all with using Brook1966's suggestions above

Sub test()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sConnString As String

'
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                              & "Data Source=" _
                              & frmCuesheetCreator.txtCuesheetLocation.Text
conn.Open sConnString
rs.Open " SELECT * FROM " & cboScene.List(cboScene.ListIndex), conn, adOpenKeyset, adLockOptimistic
PopulateFlexGrid MSFlexGrid1, rs
'
rs.Close
conn.Close
Dim R As Long
Dim C As Long
Dim cell_wid As Single
Dim col_wid As Single
Dim s As Integer
Dim t As Integer
Dim q As Integer
Dim cellval As String
Dim lastcellval As String
Dim thiscellval As String


    For C = 0 To MSFlexGrid1.Cols - 1
        col_wid = 0
        For R = 0 To MSFlexGrid1.Rows - 1
            cell_wid = TextWidth(MSFlexGrid1.TextMatrix(R, C))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next R
        MSFlexGrid1.ColWidth(C) = col_wid + 120
    Next C

MSFlexGrid1.ColWidth(0) = 0
MSFlexGrid1.ColAlignment(2) = 1
MSFlexGrid1.ColAlignment(3) = 1
MSFlexGrid1.ColAlignment(4) = 1
MSFlexGrid1.ColAlignment(5) = 1
MSFlexGrid1.ColAlignment(6) = 1


For s = 1 To MSFlexGrid1.Rows - 1
MSFlexGrid1.col = 4
MSFlexGrid1.Row = s
cellval = MSFlexGrid1.Text
Select Case cellval

Case "1"
MSFlexGrid1.CellBackColor = &HF221B3
MSFlexGrid1.CellForeColor = &HF221B3
Case "2"
MSFlexGrid1.CellBackColor = vbBlue
MSFlexGrid1.CellForeColor = vbBlue
Case "3"
MSFlexGrid1.CellBackColor = vbYellow
MSFlexGrid1.CellForeColor = vbYellow
Case "4"
MSFlexGrid1.CellBackColor = vbGreen
MSFlexGrid1.CellForeColor = vbGreen
Case "5"
MSFlexGrid1.CellBackColor = &HE6DFE0
MSFlexGrid1.CellForeColor = &HE6DFE0
Case "6"
MSFlexGrid1.CellBackColor = &H80FF&
MSFlexGrid1.CellForeColor = &H80FF&
Case Else
MSFlexGrid1.CellBackColor = vbWhite
MSFlexGrid1.CellForeColor = vbBlack
End Select
Next s
''GoTo skip
lastcellval = ""
For t = 1 To MSFlexGrid1.Rows - 1

MSFlexGrid1.col = 0
MSFlexGrid1.Row = t
thiscellval = MSFlexGrid1.Text

If lastcellval = thiscellval Then
MSFlexGrid1.col = 1
MSFlexGrid1.Row = t
MSFlexGrid1.CellBackColor = vbWhite
MSFlexGrid1.CellForeColor = vbWhite
MSFlexGrid1.col = 2
MSFlexGrid1.Row = t
MSFlexGrid1.CellBackColor = vbWhite
MSFlexGrid1.CellForeColor = vbWhite
MSFlexGrid1.col = 3
MSFlexGrid1.Row = t
MSFlexGrid1.CellBackColor = vbWhite
MSFlexGrid1.CellForeColor = vbWhite
Else
MSFlexGrid1.col = 1
MSFlexGrid1.Row = t
MSFlexGrid1.CellBackColor = vbBlack
MSFlexGrid1.CellForeColor = vbBlack
MSFlexGrid1.col = 2
MSFlexGrid1.Row = t
MSFlexGrid1.CellBackColor = vbBlack
MSFlexGrid1.CellForeColor = vbBlack
MSFlexGrid1.col = 3
MSFlexGrid1.Row = t
MSFlexGrid1.CellBackColor = vbBlack
MSFlexGrid1.CellForeColor = vbBlack
End If
skip:
MSFlexGrid1.col = 0
MSFlexGrid1.Row = t
lastcellval = MSFlexGrid1.Text
Next t
End Sub

Select allOpen in new window