Solved

Help with changing DataBridView's cell colors for unmatching data

Posted on 2014-03-06
6
276 Views
Last Modified: 2014-03-09
Hi,

I'm trying to use the code below to change the backcolor and textcolor of cell where the values are different when comparing rows as I loop through the Grid, but I receive the follow error:

Operator '=' is not defined for type 'DBNull' and type 'DBNull'.

For example (More columns in actual project) if my grid contains four rows the following columns:

CTRY      Item
BEL        BMW
BEL        Toyota
USA        Jeep
USA        Jeep

I would like to change the color of the item column for the first two rows because the data in the Item column is not the same.

Code:
 Dim i, j As Integer
        For i = 1 To C1Screen2.Rows.Count - 1
            For j = 1 To C1Screen2.Columns.Count - 1
                If C1Screen2.Rows(i - 1).Cells("CTRY").Value = C1Screen2.Rows(i).Cells("CTRY").Value Then
                    If Not C1Screen2.Rows(i - 1).Cells(j).Value = C1Screen2.Rows(i).Cells(j).Value Then
                        C1Screen2.Rows(i - 1).Cells(j).Style.BackColor = Drawing.Color.Red
                        C1Screen2.Rows(i - 1).Cells(j).Style.ForeColor = Drawing.Color.White
                        C1Screen2.Rows(i).Cells(j).Style.BackColor = Drawing.Color.Red
                        C1Screen2.Rows(i).Cells(j).Style.ForeColor = Drawing.Color.White
                    Else
                        C1Screen2.Rows(i - 1).Cells(j).Style.BackColor = Drawing.Color.Blue
                        C1Screen2.Rows(i - 1).Cells(j).Style.ForeColor = Drawing.Color.White
                        C1Screen2.Rows(i).Cells(j).Style.BackColor = Drawing.Color.Blue
                        C1Screen2.Rows(i).Cells(j).Style.ForeColor = Drawing.Color.White
                    End If
                End If
            Next
        Next

Open in new window

I used this code a while back in a different project not certain why it's not working now,

Thanks,

Victor
0
Comment
Question by:vcharles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39909141
I would suggest you add another if statement before following

If C1Screen2.Rows(i - 1).Cells("CTRY").Value = C1Screen2.Rows(i).Cells("CTRY").Value

as below
If Not IsDBNull( C1Screen2.Rows(i - 1).Cells("CTRY").Value) AND Not IsDBNull(C1Screen2.Rows(i).Cells("CTRY").Value)
0
 

Author Comment

by:vcharles
ID: 39909269
Hi,

I tried you approach but received error message:

Operator '=' is not defined for string " L534A1" and type default.  (L534A1 is the value in first row of the third column)

On Line :

If Not C1Screen2.Rows(i - 1).Cells(j).Value = C1Screen2.Rows(i).Cells(j).Value Then

Thanks.
0
 
LVL 28

Accepted Solution

by:
Ark earned 500 total points
ID: 39911511
This is because rows(rows.count-1) points to new row (if AllowUserToAddRow property is true), which contains null values - probably in your previous project this property was set to false. Just check
For i = 1 To C1Screen2.Rows.Count - 1
    If C1Screen2.Rows(i).IsNewRow Then Continue For '<=== ADDED

Open in new window

BTW, here is an example for multiple columns
    Private affectedColumns As String() = {"CTRY", "Item"} ' <== Add more columns you need to compare

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With DataGridView1
            .Columns.Add("CTRY", "CTRY")
            .Columns.Add("Item", "Item")
            .Rows.Add("BEL", "BMW")
            .Rows.Add("BEL", "TOYOTA")
            .Rows.Add("USA", "JEEP")
            .Rows.Add("USA", "JEEP")
        End With
    End Sub

    Private Function dgvContainsRow(ByVal dgv As DataGridView, ByVal row As DataGridViewRow) As Boolean
        For Each r As DataGridViewRow In dgv.Rows
            If r.IsNewRow OrElse r.Equals(row) Then Continue For
            Dim matchCount As Integer
            For Each c In affectedColumns
                If r.Cells(c).Value.Equals(row.Cells(c).Value) Then
                    matchCount += 1
                End If
            Next
            If matchCount = affectedColumns.Count Then Return True
        Next
        Return False
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        For Each r As DataGridViewRow In DataGridView1.Rows
            If r.IsNewRow Then Continue For
            Dim foreColor = Color.White
            Dim backColor = Color.Red
            If dgvContainsRow(DataGridView1, r) Then
                backColor = Color.Blue
            End If
            For Each c As DataGridViewCell In r.Cells
                c.Style.BackColor = backColor
                c.Style.ForeColor = foreColor
            Next
        Next
    End Sub

Open in new window

0
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 

Author Comment

by:vcharles
ID: 39911608
Thanks, will try it and get back to you.
0
 

Author Comment

by:vcharles
ID: 39912510
Hi,

When I add If C1Screen2.Rows(i).IsNewRow Then  in my code thge error does not occur but

  If Not C1Screen2.Rows(i - 1).Cells(j).Value = C1Screen2.Rows(i).Cells(j).Value Then
                 
does not execute.

I tried you code but it does not consider that the SN values must be the same before comparing the rows, is there a way to modify it to work only for identical SN values?

For example If I have:

  SN               CTRY      Item
 10411           BEL        BMW
 10411           BEL        Toyota
 10421           USA        Jeep
 10451           USA        Jeep

Only the first two rows should be compared because they both have SN = 10411. Also Is it possible to only change the color of the cells that do not match instead of the entire row.

Thanks,

Victor

Thanks,

Victor
0
 

Author Closing Comment

by:vcharles
ID: 39915913
Thank You.
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question