Results 1 to 29 of 29

Thread: code is coloring a whole column instead of cells

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    code is coloring a whole column instead of cells

    Good day,

    I have a piece of code that should paint cells with a special condition but instead of doing that it colors the whole column

    Code:
    Dim Expdate As Date
            Dim tsDate As TimeSpan
            For Each row As DataGridViewRow In dgvCert.Rows
                For i As Integer = 0 To row.Cells.Count - 1
                    Select Case (row.Cells(i).Value) IsNot DBNull.Value
                        Case (row.Cells(16).Value) IsNot DBNull.Value
                            row.Cells(11).Style.BackColor = Color.Blue
                        Case (row.Cells(11).Value) IsNot DBNull.Value
                            Expdate = row.Cells(11).Value
                            tsDate = Now().Subtract(Expdate)
                            If (tsDate.TotalDays > -90) And (tsDate.TotalDays < 90) Then
                                row.Cells(11).Style.BackColor = Color.LightBlue
                            End If
                            If tsDate.TotalDays >= 90 Then
                                row.Cells(11).Style.BackColor = Color.Red
                            End If
                    End Select
                Next
            Next
    Where have i made the mistake
    Thank you

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: code is coloring a whole column instead of cells

    Your Select Case doesn't make sense. Please describe what your actual rules are and then we can tell you how to implement them because what you have now can't possibly do anything useful.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    I have list of certificates loaded in a datagridview
    A certificate has a lifetime of 5 years and every year it has an annual survey this is a window of 90 days before and 90 days after every year if in that time no annual has done the certificate expires if the annual is done the cell has to go to the normal white color

    certificate issued 12-12-2013

    First annual 12-09-2014 till 12-03-2015

    Second annual 12-09-2015 till 12-03-2016

    third annual 12-09-2016 till 12-03-2017

    fourth annual 12-09-2017 till 12-03-2018

    Expiry 12-09-2018 till 12-12-2018

    The expiry cell is 11 and the first annual is cell no 16 i could not get it working with if statements so i tried the case instead.

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: code is coloring a whole column instead of cells

    Hi,

    hope jmc dosn't mind me chipping in.
    I have seen this the other day and thought it might be better to add another field to the Database
    called Status_DOC (Integer)
    default would be 0 to 3 (status for color White)
    1 = your other color
    2 = again other color

    it will be easier to use a Sql-Statement to change the Status from 0 to 1 or whatever

    when you change the status call this
    Code:
        Private Sub ApplyFormatting()
    
            For Each row As DataGridViewRow In DataGridView1.Rows
                Select Case CInt(row.Cells(3).Value) Edit: Cell(3) would be Status_DOC with 0 to 3
                    Case Is = 0 'no action
                        row.Cells(1).Style.BackColor = Color.AntiqueWhite ' Edit: Cell(1) the Surname
                    Case Is = 1
                        row.Cells(1).Style.BackColor = Color.Blue
                    Case Is = 2
                        row.Cells(1).Style.BackColor = Color.BurlyWood
    
                    Case Else
                        'other stuff
                End Select
            Next
    End Sub
    regards
    Chris
    Last edited by ChrisE; Sep 28th, 2017 at 07:08 AM.
    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.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    Why would my case statement make no sense? It is working but only instead of that 1 row which the row.cells(16) cell number 11 has to be painted it paint the whole column

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    Okay i came up with this code and it is working at least so far i can see
    Code:
    Dim tsDate As TimeSpan
    
            For Each row As DataGridViewRow In dgvCert.Rows
    
    
                If row.Cells(11).Value IsNot DBNull.Value Then
                    tsDate = CDate(row.Cells(11).Value).Subtract(Now)
    
                    If tsDate.TotalDays < 0 Then
    
                        row.Cells(11).Style.BackColor = Color.Red
    
    
                    ElseIf tsDate.TotalDays < 90 And tsDate.TotalDays > 0 Then
    
                        row.Cells(11).Style.BackColor = Color.LightBlue
    
                    End If
                End If
                If row.Cells(16).Value IsNot DBNull.Value Then
                    row.Cells(11).Style.BackColor = Color.Blue
                End If
            Next

    but now my program gets scrambled
    all my textboxes are confused and if i use the scrollbar in datgridview to see the last colums it is looking like it is looping throug the datgridview
    Name:  Knipsel.jpg
Views: 197
Size:  15.0 KB

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: code is coloring a whole column instead of cells

    Hi ,

    I gave you a diffrent approach in Post#4.

    Think about what you are doing in Post#6, if your Datagrid would have more that 1000 records ...
    need I say more?

    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.

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    yes i can see that , isnt there another way as i not would like any more columns in my datagrid view.

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: code is coloring a whole column instead of cells

    Where did you put the code you posted in post #6?

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    in a public sub applyformatting called from celformatting
    and in celformatting private sub

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: code is coloring a whole column instead of cells

    Quote Originally Posted by zubenubie View Post
    in a public sub applyformatting called from celformatting
    and in celformatting private sub
    Well that's your problem then. The CellFormatting event is raised quite a lot. It is raised for a specific cell when it needs to be formatted so you should be implementing formatting code for just that cell. The code you posted loops through every row and potentially makes changes for all of them, so you're going to be processing the whole grid over and over. Make your mind up. Process the whole grid once or else process each specific cell as required.

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    can you give me a hint how and with what code i can get this done

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

    Re: code is coloring a whole column instead of cells

    The hint is that for each time cellformating is called on 1 cell, ur code will run for each and every one in the data grid, means it will start formatin cell 0 from row 0, goes tro all every other cels, passes to cell 1 from row 0 and repeats.... and repeats...

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    How can i call it then a single time, i have been looking but cant find it, sorry to bother but i am learning vb net due to my mistakes and google

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

    Re: code is coloring a whole column instead of cells

    For that, use the Event CellFormatting, place a condition on the column index:

    If colum index = X then

    Execute my code

    End If

    You have that on the other thread i helped you.
    And get rid of the For Each, and the For i To... you dont need them in this case.

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    Code:
      Dim tsDate As TimeSpan
    
    
            If e.ColumnIndex = 11 Then
    
                If e.Value IsNot DBNull.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.LightBlue
                    End If
                End If
            End If
            If e.ColumnIndex = 11 Then
    
                Dim CRW = From RS In dgvCert.Rows Where RS.cells(2).value = dgvCert.Rows(e.RowIndex).Cells(2).Value
    
                For Each R In CRW
    
    
                    If Not IsDBNull(R.cells(16).value) Then
    
    
                        e.CellStyle.BackColor = Color.LightBlue
    
                    End If
    
    
    
                Next
    
            End If
    Thanks again Mike. above code does work. I thought it was easier using the For Each row As DataGridViewRow In DataGridView1.Rows because it was for 1 dgv only

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

    Re: code is coloring a whole column instead of cells

    Im confused with your code...

    see if this works:


    Code:
    If e.ColumnIndex = 11 Then
    
                Dim tsDate As TimeSpan
    
                If Not IsDBNull(e.Value) AndAlso IsDBNull(dgvCert.Rows(e.RowIndex).Cells(16).Value) Then
                    tsDate = CDate(dgvCert.Rows(e.RowIndex).Cells(11).Value).Subtract(Now)
    
                    If tsDate.TotalDays < 0 Then
    
                        dgvCert.Rows(e.RowIndex).Cells(11).Style.BackColor = Color.Red
    
    
                    ElseIf tsDate.TotalDays < 90 And tsDate.TotalDays > 0 Then
    
                        dgvCert.Rows(e.RowIndex).Cells(11).Style.BackColor = Color.LightBlue
    
                    End If
    
                Else
    
                    dgvCert.Rows(e.RowIndex).Cells(11).Style.BackColor = Color.Blue
    
                End If
    
            End If

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    the cell no16 gives only a color if it is filled with a dat thus isnot dbnul. with your code it changes the cell no 11 still to red even when cell 16 is filled

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

    Re: code is coloring a whole column instead of cells

    Ok, i m not testing the code now, was done on mi head and sugested to avoind have extra If and For Each.

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

    Re: code is coloring a whole column instead of cells

    Code:
    If e.ColumnIndex = 11 Then
    
                Dim tsDate As TimeSpan
    
                If e.Value IsNot DBNull.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.LightBlue
                    End If
    
                End If
    
                'Any way you can move this here, and i still think the code below can be incorporated in the If statement above
                'Alow i can't test it now
                Dim CRW = From RS In dgvCert.Rows Where RS.cells(2).value = dgvCert.Rows(e.RowIndex).Cells(2).Value
    
                For Each R In CRW
    
                    If Not IsDBNull(R.cells(16).value) Then
    
    
                        e.CellStyle.BackColor = Color.LightBlue
    
                    End If
    
                Next
    
            End If

  21. #21

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    No problemanyway thank you for pointing me to the right solution

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    Got the full code for 4 duedates

    Code:
    Private Sub dgvCert_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles dgvCert.CellFormatting
    
    
            Dim tsDate As TimeSpan
    
    
            If e.ColumnIndex = 12 Then
    
                If e.Value IsNot DBNull.Value Then
                    tsDate = CDate(e.Value).Subtract(Now)
    
                    If tsDate.TotalDays < -90 Then
    
                        e.CellStyle.BackColor = Color.Red
    
    
                    ElseIf tsDate.TotalDays < 90 And tsDate.TotalDays > -90 Then
    
                        e.CellStyle.BackColor = Color.LightBlue
                    End If
                End If
            End If
            If e.ColumnIndex = 13 Then
    
                If e.Value IsNot DBNull.Value Then
                    tsDate = CDate(e.Value).Subtract(Now)
    
                    If tsDate.TotalDays < -90 Then
    
                        e.CellStyle.BackColor = Color.Red
    
    
                    ElseIf tsDate.TotalDays < 90 And tsDate.TotalDays > -90 Then
    
                        e.CellStyle.BackColor = Color.LightBlue
                    End If
                End If
            End If
            If e.ColumnIndex = 14 Then
    
                If e.Value IsNot DBNull.Value Then
                    tsDate = CDate(e.Value).Subtract(Now)
    
                    If tsDate.TotalDays < -90 Then
    
                        e.CellStyle.BackColor = Color.Red
    
    
                    ElseIf tsDate.TotalDays < 90 And tsDate.TotalDays > -90 Then
    
                        e.CellStyle.BackColor = Color.LightBlue
                    End If
                End If
            End If
            If e.ColumnIndex = 15 Then
    
                If e.Value IsNot DBNull.Value Then
                    tsDate = CDate(e.Value).Subtract(Now)
    
                    If tsDate.TotalDays < -90 Then
    
                        e.CellStyle.BackColor = Color.Red
    
    
                    ElseIf tsDate.TotalDays < 90 And tsDate.TotalDays > -90 Then
    
                        e.CellStyle.BackColor = Color.LightBlue
                    End If
                End If
            End If
            If e.ColumnIndex = 12 Then
    
                Dim CRW = From RS In dgvCert.Rows Where RS.cells(0).value = dgvCert.Rows(e.RowIndex).Cells(0).Value
    
                For Each R In CRW
    
                    If Not IsDBNull(R.cells(17).value) Then
    
                        e.CellStyle.BackColor = Color.LightGreen
    
                    End If
                Next
            End If
            If e.ColumnIndex = 13 Then
    
                Dim CRW = From RS In dgvCert.Rows Where RS.cells(0).value = dgvCert.Rows(e.RowIndex).Cells(0).Value
    
                For Each R In CRW
    
                    If Not IsDBNull(R.cells(18).value) Then
    
                        e.CellStyle.BackColor = Color.LightGreen
    
                    End If
                Next
            End If
            If e.ColumnIndex = 14 Then
    
                Dim CRW = From RS In dgvCert.Rows Where RS.cells(0).value = dgvCert.Rows(e.RowIndex).Cells(0).Value
    
                For Each R In CRW
    
                    If Not IsDBNull(R.cells(19).value) Then
    
                        e.CellStyle.BackColor = Color.LightGreen
    
                    End If
                Next
            End If
            If e.ColumnIndex = 15 Then
    
                Dim CRW = From RS In dgvCert.Rows Where RS.cells(0).value = dgvCert.Rows(e.RowIndex).Cells(0).Value
    
                For Each R In CRW
    
                    If Not IsDBNull(R.cells(20).value) Then
    
                        e.CellStyle.BackColor = Color.LightGreen
    
                    End If
                Next
            End If
    this gives the folowing result in my datagrid view

    Name:  naamloos.jpg
Views: 145
Size:  11.3 KB
    Attached Images Attached Images  

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

    Re: code is coloring a whole column instead of cells

    as i mention before you still checking twice for each column:

    Your code:
    Code:
    If e.ColumnIndex = 12 Then
    
                If e.Value IsNot DBNull.Value Then
                    tsDate = CDate(e.Value).Subtract(Now)
    
                    If tsDate.TotalDays < -90 Then
    
                        e.CellStyle.BackColor = Color.Red
    
    
                    ElseIf tsDate.TotalDays < 90 And tsDate.TotalDays > -90 Then
    
                        e.CellStyle.BackColor = Color.LightBlue
                    End If
                End If
            End If
    ' Then you have the code for each column to perform first task and after you have again
    
    If e.ColumnIndex = 12 Then
    
                Dim CRW = From RS In dgvCert.Rows Where RS.cells(0).value = dgvCert.Rows(e.RowIndex).Cells(0).Value
    
                For Each R In CRW
    
                    If Not IsDBNull(R.cells(17).value) Then
    
                        e.CellStyle.BackColor = Color.LightGreen
    
                    End If
                Next
            End If
    
    'Second action
    Try it like this:
    Code:
    If e.ColumnIndex = 12 Then
    
                If e.Value IsNot DBNull.Value Then
                    tsDate = CDate(e.Value).Subtract(Now)
    
                    If tsDate.TotalDays < -90 Then
    
                        e.CellStyle.BackColor = Color.Red
    
    
                    ElseIf tsDate.TotalDays < 90 And tsDate.TotalDays > -90 Then
    
                        e.CellStyle.BackColor = Color.LightBlue
                    End If
                End If
    
                Dim CRW = From RS In dgvCert.Rows Where RS.cells(0).value = dgvCert.Rows(e.RowIndex).Cells(0).Value
    
                For Each R In CRW
    
                    If Not IsDBNull(R.cells(17).value) Then
    
                        e.CellStyle.BackColor = Color.LightGreen
    
                    End If
                Next
    
            End If
    Last edited by Mike Storm; Sep 28th, 2017 at 12:16 PM.

  24. #24

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    yep you are right checked the column twice, thanks man

  25. #25

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    Don't know what is happening here with the dates
    i have a piece of code that add and deduct 3 months of a date the adding works but deduct doesnt work
    Code:
    If Not IsDBNull(R.cells(9).value) Then
                        date1 = CDate(R.cells(9).value).AddMonths(3)
                        date2 = CDate(R.cells(9).value).AddMonths(-3)
    
                        date3 = CDate(R.cells(9).value)
                        If date3 < date2 AndAlso date3 > date1 Then
                            e.CellStyle.BackColor = Color.Red
    i have tried adddays and more but with the negative it doesnt go, i keep getting error

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

    Re: code is coloring a whole column instead of cells

    Try thys:

    Code:
    date2 = DateAdd("m", -3, CDate(R.cells(9).value).AddMonths(-3))
    Correct code is this, but you still need to check if it requires formating R.cells(9).value

    Code:
    date2 = DateAdd("m", -3, CDate(R.cells(9).value))
    Last edited by Mike Storm; Sep 29th, 2017 at 07:21 PM. Reason: Sorry, the copy past went bad

  27. #27

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    No is also not working i still get the error

    the added or subtracted value results in a datetime which can not be shown.

    Very strange

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

    Re: code is coloring a whole column instead of cells

    You may need to do some formating on the values.

  29. #29

    Thread Starter
    Addicted Member
    Join Date
    Mar 2017
    Location
    Netherlands
    Posts
    136

    Re: code is coloring a whole column instead of cells

    yes job for tomorrow thanks again

Posting Permissions

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



Click Here to Expand Forum to Full Width