-
Sep 28th, 2017, 06:06 AM
#1
Thread Starter
Addicted Member
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
-
Sep 28th, 2017, 06:16 AM
#2
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.
-
Sep 28th, 2017, 06:33 AM
#3
Thread Starter
Addicted Member
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.
-
Sep 28th, 2017, 06:53 AM
#4
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.
-
Sep 28th, 2017, 07:44 AM
#5
Thread Starter
Addicted Member
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
-
Sep 28th, 2017, 09:16 AM
#6
Thread Starter
Addicted Member
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
-
Sep 28th, 2017, 09:33 AM
#7
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.
-
Sep 28th, 2017, 09:40 AM
#8
Thread Starter
Addicted Member
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.
-
Sep 28th, 2017, 09:40 AM
#9
Re: code is coloring a whole column instead of cells
Where did you put the code you posted in post #6?
-
Sep 28th, 2017, 09:45 AM
#10
Thread Starter
Addicted Member
Re: code is coloring a whole column instead of cells
in a public sub applyformatting called from celformatting
and in celformatting private sub
-
Sep 28th, 2017, 09:59 AM
#11
Re: code is coloring a whole column instead of cells
Originally Posted by zubenubie
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.
-
Sep 28th, 2017, 10:01 AM
#12
Thread Starter
Addicted Member
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
-
Sep 28th, 2017, 10:15 AM
#13
Hyperactive Member
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...
-
Sep 28th, 2017, 10:39 AM
#14
Thread Starter
Addicted Member
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
-
Sep 28th, 2017, 10:47 AM
#15
Hyperactive Member
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.
-
Sep 28th, 2017, 11:09 AM
#16
Thread Starter
Addicted Member
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
-
Sep 28th, 2017, 11:16 AM
#17
Hyperactive Member
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
-
Sep 28th, 2017, 11:26 AM
#18
Thread Starter
Addicted Member
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
-
Sep 28th, 2017, 11:29 AM
#19
Hyperactive Member
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.
-
Sep 28th, 2017, 11:32 AM
#20
Hyperactive Member
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
-
Sep 28th, 2017, 11:33 AM
#21
Thread Starter
Addicted Member
Re: code is coloring a whole column instead of cells
No problemanyway thank you for pointing me to the right solution
-
Sep 28th, 2017, 11:55 AM
#22
Thread Starter
Addicted Member
Re: code is coloring a whole column instead of cells
-
Sep 28th, 2017, 12:02 PM
#23
Hyperactive Member
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.
-
Sep 28th, 2017, 12:59 PM
#24
Thread Starter
Addicted Member
Re: code is coloring a whole column instead of cells
yep you are right checked the column twice, thanks man
-
Sep 28th, 2017, 03:07 PM
#25
Thread Starter
Addicted Member
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
-
Sep 28th, 2017, 03:26 PM
#26
Hyperactive Member
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
-
Sep 28th, 2017, 03:31 PM
#27
Thread Starter
Addicted Member
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
-
Sep 28th, 2017, 03:32 PM
#28
Hyperactive Member
Re: code is coloring a whole column instead of cells
You may need to do some formating on the values.
-
Sep 28th, 2017, 03:43 PM
#29
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|