[RESOLVED] Color FlexGrid Based on Data in Cell

[DJHotIce]

I have a column and I want to paint it certain colors depending on the text.

I found some code and tried to recycle it but that didnt work... Here is the code

Dim lrow, lcol As Integer
    Dim lcolor As String
    
        With MSFlexGrid1
                For lrow = .FixedRows To .Rows
                    .Row = lrow
                    If UCase(.TextMatrix(lrow, 1)) = "BREAK" Then
                        lcolor = vbGreen
                        For lcol = 0 To 1
                            .Col = lcol
                            .CellBackColor = lcolor
                        Next lcol
                    ElseIf .CellBackColor <> vbGreen Then
                        lcolor = vbRed
                    End If
                Next lrow
        End With

It goes into an infinity loop and crashes the IDE.

[szlamany]

Put some DEBUG.PRINT statements above the WITH and in the WITH.

My guess is that, since when you change .COL or .ROW, you trigger FLEXGRID events like ROWCHANGE and COLCHANGE that you are getting yourself in trouble with that.

Maybe .ENABLED=FALSE will help?

[brucevde]

That code alone will not cause an infinite loop but will cause an "Invalid Row" error. The For Loop should be

For lrow = .FixedRows To .Rows -1

Do you have code in any of the FlexGrid events, such as EnterCell or RowColChange?

[DJHotIce]

Right Now that code is in the LeaveCell attribute, I wish to update every time a user changes something.

[szlamany]

Then I believe that if you put a debug.print at the top of the LEAVE CELL event you will see if get triggered repeatedly, as you change .COL values.

I'm guessing though...

[DJHotIce]

Here is an updated working version of the script. I used a Bool Operator to keep it from going haywire.

Private Function ColorRows(pLookFor As String, pLookRow As Integer, ByVal pColor As String)
    Dim lrow, lcol As Integer
        With MSFlexGrid1
            FlexGridDone = False
                .Redraw = False
                For lrow = .FixedRows To .Rows - 1
                    .Row = lrow
                    If UCase(.TextMatrix(lrow, pLookRow)) = pLookFor Then
                        For lcol = 1 To (TotalCols - 1)
                            .Col = lcol
                            .CellBackColor = pColor
                        Next lcol
                    End If
                Next lrow
                .Redraw = True
        End With
        FlexGridDone = True
End Function

[szlamany]

Similar to what we do - but we still use the .ENABLED property of the flex grid and then check that instead.

Same thing really - just need a way to not "re-iterate" when doing something like that from the LEAVECELL event.

The FLEXGRID drives me crazy because of stuff like that!