VS 2015 How to color a cell in a datagridview-VBForums
Results 1 to 35 of 35

Thread: How to color a cell in a datagridview

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    How to color a cell in a datagridview

    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

    Name:  001.jpg
Views: 135
Size:  17.3 KB


    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

  2. #2
    Addicted Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    153

    Re: How to color a cell in a datagridview

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

  3. #3
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

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

    Code:
    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.
    Last edited by Mike Storm; Sep 25th, 2017 at 05:26 AM.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    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.

  5. #5
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    maybe this:

    Code:
    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.

  6. #6
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    Also CellFormating event maybe better suited for that:

    Code:
    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

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    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

  8. #8
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    574

    Re: How to color a cell in a datagridview

    Hi,

    take the value like Mike said from the Cell Value

    see if this works for you..

    Code:
    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
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    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
    Last edited by zubenubie; Sep 25th, 2017 at 08:14 AM.

  10. #10
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

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

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    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

  12. #12
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    Then you will have to check if value IsDBNull

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

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

  14. #14
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    can you post the code?

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    Code:
     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

  16. #16
    Addicted Member
    Join Date
    Aug 2017
    Location
    Nigeria
    Posts
    153

    Re: How to color a cell in a datagridview

    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

  17. #17
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    Try something like this:

    Code:
    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

  18. #18

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    Name:  Knipsel.jpg
Views: 33
Size:  28.6 KB

    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
    Name:  1.PNG
Views: 32
Size:  18.5 KB

  19. #19
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    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?

  20. #20

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    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

  21. #21

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    @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.

    Code:
    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

  22. #22
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    Sorry, didnt understand, did it solve your problem?

  23. #23

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    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

  24. #24
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    Have a look on this:

    Code:
    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

  25. #25
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

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

  26. #26
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    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:

    Code:
    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
    Last edited by Mike Storm; Sep 25th, 2017 at 10:28 AM.

  27. #27

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    Quote Originally Posted by Mike Storm View Post
    Have a look on this:

    Code:
    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)

  28. #28

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

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

  29. #29
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

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

    Code:
     If Not IsDBNull(e.Value) Then

  30. #30
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    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"

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

  31. #31
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    And it should give you a result like this:

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

  32. #32

    Thread Starter
    Lively Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    85

    Re: How to color a cell in a datagridview

    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

  33. #33
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    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.

  34. #34
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

    Option Strict On

    Code:
    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
    Last edited by Mike Storm; Sep 25th, 2017 at 11:54 AM.

  35. #35
    Hyperactive Member Mike Storm's Avatar
    Join Date
    Jun 2017
    Location
    Belgium
    Posts
    425

    Re: How to color a cell in a datagridview

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

    Code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.