CODEWITHSUNDEEP
vb.netdatagridviewcolorsformatting
Andy Charity
Andy Charity

Reputation: 53

Conditional formatting of DataGridView cell data - Change color on negative

I was hoping to be able to use color based conditional formatting in the DefaultCellStyle.Format field for DataGridView cells, in a similar way to how Excel handles this.

For example in Excel, a format string of £#,##0.00;[Red]-£#,##0.00 will display negative values in red.

Is this supported in VB.NET ?

I am aware I can use the .CellFormatting event to conditionally change cell text color but was looking for a less bulky and restrictive way of doing this.

Upvotes: 1

Views: 5039

Answers (2)

Andy Charity
Andy Charity

Reputation: 53

By creating the following CellFormatting addition, I am able to use Excel style conditional colour formatting in the cells format field. Setting the colour for negative/positive/zero values is supported.

Format string is expected to be in the following format (all colours optional) :

[colour]<format for +value> ; [colour]<format for -value> ; [colour]<format for zero value>

..a test DGV column with conditional formatting

        c = New DataGridViewColumn
        c.Name = "AmountOUT"
        c.DataPropertyName = c.Name
        c.HeaderText = "AmountOUT"
        c.CellTemplate = New DataGridViewTextBoxCell
        c.DefaultCellStyle.Format = "[Green]£0.00;[Red]-£0.00;[Blue]zero"
        .Columns.Add(c)

..

    Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
    'Split format string to positive / negative / zero components
    Dim posnegzero As List(Of String)
    posnegzero = e.CellStyle.Format.Split(CChar(";")).ToList

    Dim coloursPNZ As New List(Of String)
    Dim remainderformatPNZ As String = ""

    For Each s As String In posnegzero
        If s.Contains("[") And s.Contains("]") Then
            'Extract [xxx] contents
            coloursPNZ.Add(s.Substring(s.IndexOf("[") + 1, s.IndexOf("]") - s.IndexOf("[") - 1))
            'Append rebuilt format excluding [xxx]
            remainderformatPNZ &= s.Substring(0, s.IndexOf("[")) & s.Substring(s.IndexOf("]") + 1, s.Length - s.IndexOf("]") - 1) & ";"
        Else
            coloursPNZ.Add("")
            remainderformatPNZ &= s & ";"
        End If
    Next

    'Set format excluding any [xxx] components
    e.CellStyle.Format = remainderformatPNZ

    'Check for positive value
    If Val(e.Value) > 0 And coloursPNZ.Count >= 1 Then
        If coloursPNZ(0) <> "" Then
            e.CellStyle.ForeColor = Color.FromName(coloursPNZ(0))
        End If
    End If

    'Check for negative value
    If Val(e.Value) < 0 And coloursPNZ.Count >= 2 Then
        If coloursPNZ(1) <> "" Then
            e.CellStyle.ForeColor = Color.FromName(coloursPNZ(1))
        End If
    End If

    'Check for zero value
    If Val(e.Value) = 0 And coloursPNZ.Count >= 3 Then
        If coloursPNZ(2) <> "" Then
            e.CellStyle.ForeColor = Color.FromName(coloursPNZ(2))
        End If
    End If
End Sub

Upvotes: 1

&#248;mi
&#248;mi

Reputation: 521

Dim dgv As DataGridView = Me.DataGridView1 

    For i As Integer = 0 To dgv.Rows.Count - 1
        For ColNo As Integer = 4 To 7 ' number columns
            If Not dgv.Rows(i).Cells(ColNo).Value < 0 Then

                dgv.Rows(i).Cells(ColNo).Style.BackColor =  vbcolor.Red
            End If
        Next
    Next

checking for negative values lookout for strings format and check accordingly

Tryparse will convert the input to an integer if it succeeds - you don't need both the comps and value variables. Here's an example of how it works:

Dim comps As Integer
Dim input As String = "im not an integer"
Dim input2 As String = "2"

'tryparse fails, doesn't get into comps < 0 comparison
If Integer.TryParse(input, comps) Then
    If comps < 0 Then
        'do something
    End If
Else
   'I'm not an integer!
End If

'tryparse works, goes into comps < 0 comparison
If Integer.TryParse(input2, comps) Then
    If comps < 0 Then
        'do something
    End If
End If

Upvotes: 0

Related Questions