• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Help with changing DataBridView's cell colors for unmatching data

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
vcharles
Asked:
vcharles
  • 4
1 Solution
 
CodeCruiserCommented:
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
 
vcharlesAuthor Commented:
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
 
ArkCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
vcharlesAuthor Commented:
Thanks, will try it and get back to you.
0
 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
Thank You.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now