How to color a cell in a datagridview

[zubenubie]

Good day,

In below picture i have 2 datagridviews the top datagridview is populated with crew table and below with document table with a relation on crewid




in the document datagridview i have painted the cell document and expirydate with rowprepaint and cellformatting.
now i want to paint the surname in the crew datagridview(top one) what is the best way to do this?

Thank you

[TATARPRO]

are all the surnames going to be on the same column?

[Mike Storm]

Hi,
If you want the entire column to have the same color independent of row:

DGV.Columns(1).DefaultCellStyle.BackColor = Color.Green

If the datagridview is bound to a table or columns are already created you can do this in designer by editing the columns collection and set backcolor in Datagridviewcellstyle.

[zubenubie]

Hello

the cellformatting is based on a calculation if the date is getting within 90 days of the expiry date the cell gets colored coral and when the date is past the expiry date the cell will color red.

if in docdatagridview crewid 8 has a doc within these dates then that crewid 8 surname cell in the crew datagridview gets colored.

[Mike Storm]

maybe this:

Private Sub DGV_RowPrePaint(sender As Object, e As DataGridViewRowPrePaintEventArgs) Handles DGV.RowPrePaint
        If CInt(DGV.Rows(e.RowIndex).Cells(1).Value) > 10 Then

            DGV.Rows(e.RowIndex).Cells(1).Style.BackColor = Color.Red

        End If
    End Sub

You will need to change the datatype on the if.

[Mike Storm]

Also CellFormating event maybe better suited for that:

Private Sub DGV_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DGV.CellFormatting
        If CInt(DGV.Rows(e.RowIndex).Cells(1).Value) > 10 Then

            DGV.Rows(e.RowIndex).Cells(1).Style.BackColor = Color.Red

        End If
    End Sub

[zubenubie]

The crew doc and the crew are 2 different tables in the crew table there are no expirydates of the documents, so i was thinking something with a query in a cellformatting sub but i dont know how

[ChrisE]

Hi,

take the value like Mike said from the Cell Value

see if this works for you..

Private Sub ApplyFormatting()
        For Each row As DataGridViewRow In DataGridView1.Rows

            Dim d As Date = Now() 

            Select Case CDate(row.Cells(7).Value) 'your Date 

                Case Is >= d.AddDays(+15) 
                    row.Cells(7).Style.BackColor = Color.Aquamarine

                Case Is >= d.AddDays(-10)
                    row.Cells(7).Style.BackColor = Color.Brown

                Case Else
                    row.Cells(7).Style.BackColor = Color.White
            End Select
        Next
    End Sub

regards
Chris

[zubenubie]

The applyformatting sub doesnt work as there are some certificates with no expirydate so they have a null value
also 1 crewID has more then 1 document attached in the relation

[Mike Storm]

You reading the values from a table or from the datagridview?

[zubenubie]

i am reading them from a table. the expiry dates i read from crew doc table and the formatting has to happen in crewinfo table

[Mike Storm]

Then you will have to check if value IsDBNull

[zubenubie]

changed the reading of the data from the datagridview but still get DBnull conversion to date exception

[Mike Storm]

can you post the code?

[zubenubie]

 Private Sub ApplyFormatting()

        For Each row As DataGridViewRow In CrewDocDataGridView.Rows

            Dim d As Date = Now()

            Select Case CDate(row.Cells(7).Value) 'your Date 

                    Case Is >= d.AddDays(-90)
                    row.Cells(7).Style.BackColor = Color.Coral

                Case Is >= d.AddDays(0)
                    row.Cells(7).Style.BackColor = Color.Red

                Case Else
                    row.Cells(7).Style.BackColor = Color.White
            End Select
        Next
    End Sub

[TATARPRO]

if you want to paint a particular cell then put your code in cellValueChanged event so that when data is read into the datagrid the cell valueChanged will trigger

[Mike Storm]

Try something like this:

If Not IsDBNull(row.Cells(7).Value) Then

            Select Case CDate(row.Cells(7).Value) 'your Date 

                Case Is >= d.AddDays(-90)
                    row.Cells(7).Style.BackColor = Color.Coral

                Case Is >= d.AddDays(0)
                    row.Cells(7).Style.BackColor = Color.Red

                Case Else
                    row.Cells(7).Style.BackColor = Color.White
            End Select

        Else

            'Do something if dbnull or ignore

        End If

[zubenubie]

So as can be seen in the above picture crewid no 8 has multiple documents with 1 almost overdue in the document datagridview
in the upper crew datagridview i want the surname cell of crewid 8 also painted with the same color.

the 2 tables crew and document are relational

[Mike Storm]

One question...
You have 3 options, if one crew fall in to 2 of them or even in to the 3, what color shall the cell be?

[zubenubie]

in the crew datagrid view red as below datgrid view is in a tab and you maybe not can see that there are documents expiring or expired that or another tab is selected so the attention is drawn to that person

[zubenubie]

@mikestorm

I have copied you code and it is working only now on all expiry dates it is giving coral background in the document datagridview even if it is still valid for years before i used below code and that was working well.

Private Sub CrewDocDataGridView_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles CrewDocDataGridView.CellFormatting
        Dim Expdate As Date
        Dim tsDate As TimeSpan
        If e.ColumnIndex = 7 Then
            If e.Value IsNot DBNull.Value Then
                Expdate = CDate(e.Value)
                tsDate = Now().Subtract(Expdate)
                If tsDate.TotalDays > -90 Then
                    e.CellStyle.BackColor = Color.Coral

                End If
            End If
        End If
        If e.ColumnIndex = 7 Then
            If e.Value IsNot DBNull.Value Then
                Expdate = CDate(e.Value)
                tsDate = Now().Subtract(Expdate)
                If tsDate.TotalDays >= 0 Then
                    e.CellStyle.BackColor = Color.Red
                End If
            End If
        End If
    End Sub

[Mike Storm]

Sorry, didnt understand, did it solve your problem?

[zubenubie]

yes the celformattimg sub works i dont get the dbnull error no more, but like i said it is painting now all expiry dates with coral

[Mike Storm]

Have a look on this:

Dim tsDate As TimeSpan

        If e.ColumnIndex = 3 Then

            If Not IsDBNull(e.Value) And Not String.IsNullOrWhiteSpace(e.Value) Then

                tsDate = CDate(e.Value).Subtract(Now)

                If tsDate.TotalDays <= 0 Then

                    e.CellStyle.BackColor = Color.Red

                ElseIf tsDate.TotalDays < 90 And tsDate.TotalDays > 0 Then

                    e.CellStyle.BackColor = Color.Coral

                Else

                    e.CellStyle.BackColor = Color.LightGreen

                End If

            End If

        End If

[Mike Storm]

Chanfge column index back to your, i used a teste table with less columns

[Mike Storm]

As for formating the surname, you can try this, but be advised, i dont know how it will performe on a large number of records, and also there may be a better way to achive it, using Linq, wich i m not very familiary with:

If e.ColumnIndex = 1 Then

            Dim CRW = From RS In DocsDataGridView.Rows Where RS.cells(0).value = CREWSDataGridView.Rows(e.RowIndex).Cells(0).Value

            For Each R In CRW

                Dim tsDate As TimeSpan

                If Not IsDBNull(R.cells(3).value) And Not String.IsNullOrWhiteSpace(R.cells(3).value) Then

                    tsDate = CDate(R.cells(3).value).Subtract(Now)

                    If tsDate.TotalDays <= 90 Then

                        e.CellStyle.BackColor = Color.Yellow

                    End If

                End If

            Next

        End If

Live the thread open there maybe someone out there that can use Link functions better then me and is able to do this in a better fashion.

Hoppe it helped.

PS: Dont forget to change the index of the columns

[zubenubie]

Have a look on this:

Dim tsDate As TimeSpan

        If e.ColumnIndex = 3 Then

            If Not IsDBNull(e.Value) And Not String.IsNullOrWhiteSpace(e.Value) Then

                tsDate = CDate(e.Value).Subtract(Now)

                If tsDate.TotalDays <= 0 Then

                    e.CellStyle.BackColor = Color.Red

                ElseIf tsDate.TotalDays < 90 And tsDate.TotalDays > 0 Then

                    e.CellStyle.BackColor = Color.Coral

                Else

                    e.CellStyle.BackColor = Color.LightGreen

                End If

            End If

        End If

i get a mistake on the IsNullOrWhiteSpace(e.Value) (e.value)

[zubenubie]

In your next code i get a misake on RS.cells option strict doesnt allow late bindings

[Mike Storm]

I added that couse my datagrid allow add rows, and so formated also the last empty row, you can remove it, no problem

 If Not IsDBNull(e.Value) Then

[Mike Storm]

Rs.Cells corresponds to the CrewID columns, you need to change it acording to your index
I belive should be like this acording to ur pics:
You also need to change datagridview names in the falowing line of code:
Will be "FROM RS in YourDocumensDatagridviewname Where RS.cells(1).value = YourCREWSDataGridViewName.Rows(e.RowIndex).Cells(0).Value"

Dim CRW = From RS In DocsDataGridView.Rows Where RS.cells(1).value = CREWSDataGridView.Rows(e.RowIndex).Cells(0).Value

[Mike Storm]

And it should give you a result like this:

https://drive.google.com/open?id=0B9...3NLYlY4b0s2N00

[zubenubie]

I have put option strict of and it is working now, as i dont have so many documents and persons yet i have to wait and see what that will do,
but Thank you very much for this and for taking the time time for this

[Mike Storm]

I usualy work with oprion strick on, but the code u posted seem to me like u had it off, any way its simple to correct it.
And welcome.

[Mike Storm]

Option Strict On

Private Sub DocsDataGridView_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DocsDataGridView.CellFormatting

        Dim tsDate As TimeSpan

        If e.ColumnIndex = 3 Then

            If Not IsDBNull(e.Value) And Date.TryParse(CType(e.Value, String), Nothing) Then

                tsDate = CDate(e.Value).Subtract(Now)

                If tsDate.TotalDays <= 0 Then

                    e.CellStyle.BackColor = Color.Red

                ElseIf tsDate.TotalDays <= 90 And tsDate.TotalDays > 0 Then

                    e.CellStyle.BackColor = Color.Coral

                Else

                    e.CellStyle.BackColor = Color.LightGreen

                End If

            End If

        End If

    End Sub

    Private Sub CREWSDataGridView_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles CREWSDataGridView.CellFormatting

        If e.ColumnIndex = 1 Then

            Dim CRW = From RS In DocsDataGridView.Rows.Cast(Of DataGridViewRow)() Where CInt(RS.Cells(0).Value) = CInt(CREWSDataGridView.Rows(e.RowIndex).Cells(0).Value)

            For Each R In CRW

                Dim tsDate As TimeSpan

                If Not IsDBNull(R.Cells(3).Value) And Date.TryParse(CType(R.Cells(3).Value, String), Nothing) Then

                    tsDate = CDate(R.Cells(3).Value).Subtract(Now)

                    If tsDate.TotalDays <= 90 Then

                        e.CellStyle.BackColor = Color.Yellow

                    End If

                End If

            Next

        End If

    End Sub

[Mike Storm]

I had a better look in to the code, this produces the same result without the need of the For Each:

Private Sub CREWSDataGridView_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles CREWSDataGridView.CellFormatting

        If e.ColumnIndex = 1 Then

            Dim TSpan As TimeSpan = TimeSpan.Parse("90")

            Dim CRW = (From RS In DocsDataGridView.Rows.Cast(Of DataGridViewRow) Where CInt(RS.Cells(0).Value) =
                       CInt(CREWSDataGridView.Rows(e.RowIndex).Cells(0).Value) And CDate(RS.Cells(3).Value).Subtract(Now) <= TSpan).Count

            If CRW > 0 And Not String.IsNullOrWhiteSpace(CType(e.Value, String)) Then

                e.CellStyle.BackColor = Color.Yellow

            End If


        End If

    End Sub