-
Sep 25th, 2017, 05:06 AM
#1
Thread Starter
Addicted Member
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
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
-
Sep 25th, 2017, 05:16 AM
#2
Hyperactive Member
Re: How to color a cell in a datagridview
are all the surnames going to be on the same column?
-
Sep 25th, 2017, 05:22 AM
#3
Hyperactive Member
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.
-
Sep 25th, 2017, 05:56 AM
#4
Thread Starter
Addicted Member
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.
-
Sep 25th, 2017, 06:06 AM
#5
Hyperactive Member
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.
-
Sep 25th, 2017, 06:16 AM
#6
Hyperactive Member
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
-
Sep 25th, 2017, 06:27 AM
#7
Thread Starter
Addicted Member
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
-
Sep 25th, 2017, 07:14 AM
#8
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.
-
Sep 25th, 2017, 08:11 AM
#9
Thread Starter
Addicted Member
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.
-
Sep 25th, 2017, 08:13 AM
#10
Hyperactive Member
Re: How to color a cell in a datagridview
You reading the values from a table or from the datagridview?
-
Sep 25th, 2017, 08:16 AM
#11
Thread Starter
Addicted Member
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
-
Sep 25th, 2017, 08:19 AM
#12
Hyperactive Member
Re: How to color a cell in a datagridview
Then you will have to check if value IsDBNull
-
Sep 25th, 2017, 08:41 AM
#13
Thread Starter
Addicted Member
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
-
Sep 25th, 2017, 08:41 AM
#14
Hyperactive Member
Re: How to color a cell in a datagridview
-
Sep 25th, 2017, 08:44 AM
#15
Thread Starter
Addicted Member
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
-
Sep 25th, 2017, 08:54 AM
#16
Hyperactive Member
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
-
Sep 25th, 2017, 08:57 AM
#17
Hyperactive Member
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
-
Sep 25th, 2017, 09:06 AM
#18
Thread Starter
Addicted Member
Re: How to color a cell in a datagridview
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
-
Sep 25th, 2017, 09:12 AM
#19
Hyperactive Member
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?
-
Sep 25th, 2017, 09:21 AM
#20
Thread Starter
Addicted Member
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
-
Sep 25th, 2017, 09:30 AM
#21
Thread Starter
Addicted Member
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
-
Sep 25th, 2017, 09:32 AM
#22
Hyperactive Member
Re: How to color a cell in a datagridview
Sorry, didnt understand, did it solve your problem?
-
Sep 25th, 2017, 09:34 AM
#23
Thread Starter
Addicted Member
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
-
Sep 25th, 2017, 10:09 AM
#24
Hyperactive Member
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
-
Sep 25th, 2017, 10:09 AM
#25
Hyperactive Member
Re: How to color a cell in a datagridview
Chanfge column index back to your, i used a teste table with less columns
-
Sep 25th, 2017, 10:22 AM
#26
Hyperactive Member
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.
-
Sep 25th, 2017, 10:43 AM
#27
Thread Starter
Addicted Member
Re: How to color a cell in a datagridview
Originally Posted by Mike Storm
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)
-
Sep 25th, 2017, 10:45 AM
#28
Thread Starter
Addicted Member
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
-
Sep 25th, 2017, 10:46 AM
#29
Hyperactive Member
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
-
Sep 25th, 2017, 10:51 AM
#30
Hyperactive Member
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
-
Sep 25th, 2017, 10:51 AM
#31
Hyperactive Member
Re: How to color a cell in a datagridview
-
Sep 25th, 2017, 11:02 AM
#32
Thread Starter
Addicted Member
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
-
Sep 25th, 2017, 11:03 AM
#33
Hyperactive Member
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.
-
Sep 25th, 2017, 11:45 AM
#34
Hyperactive Member
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.
-
Sep 25th, 2017, 01:49 PM
#35
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|