Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

Help with changing cell color for mismatch column based on identical IDs

How do you loop through a DataGridView and based on the same SN values, compare the rows and other columns and if they don't match change the cell colors of the data in unmatched columns to red?

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 and cells for "Item" column in those two rows should be red (Text color).

Thanks,

Victor
0
vcharles
Asked:
vcharles
  • 7
  • 6
1 Solution
 
Fernando SotoCommented:
Hi Victor;

A couple of questions.

What is the data source for the data grid view, is it a DataTable object or something else?
Will there be only three columns in the data grid view or more?
Will there be a max of only two rows that can have the same SN or can there be more?
0
 
vcharlesAuthor Commented:
Hi,

The data is from a DataTable.
There will be more than three column and more than than two rows with the same SN.
Thanks.
0
 
Fernando SotoCommented:
So in the below case the first four rows have the same values in the SN column so which cells in the other two columns get colored Red?

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

Open in new window

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
vcharlesAuthor Commented:
Hi,

It would only be the Item column, but if I had another column "ItemA" where the first four rows were not the same the cells in those rows would also be red.

Victor
0
 
Fernando SotoCommented:
Hi Victor;

The following code snippet should meet your requirements as posted in the question. Seeming that the only column that is beeing tested for Distinct values is the Item column and you stateing that there are more so I added another colimn called Item2 so show how to handel multiple columns.

This query will group all rows having the same SN value which I have assumed it to be of type Integer. Then filter out all Group's having a count of 1 leaving only those Group's that have multiple Item's in them. In the Select clause I get the Row index of all the rows in the group and the count of Distinct values in the columns whos background color may change. One value needs to be created for each column whos color may change and so the reason for Item and Item2.

Dim colorCells = From row As DataGridViewRow In DataGridView1.Rows _
                 Group row By Key = row.Cells("SN").Value Into Group _
                 Where Group.Count() > 1 _
                 Select New With _
                        { _
                            .Idx = Group.Select(Function(idx) idx.Index).ToList(), _
                            .Item = Group.Select(Function(i1) i1.Cells("Item").Value).Distinct().Count(), _
                            .Item2 = Group.Select(Function(i2) i2.Cells("Item2").Value).Distinct().Count() _
                        }

For Each snGroup In colorCells
    For Each idx As Integer In snGroup.Idx
        ' One If statement for each column whos background can change color
        ' Testing Item and Item2. Add one If statement to match the the Item's in Select statement above.
        If snGroup.Item > 1 Then
            DataGridView1.Item(2, idx).Style.BackColor = Color.Red
        End If
        If snGroup.Item2 > 1 Then
            DataGridView1.Item(3, idx).Style.BackColor = Color.Red
        End If
    Next
Next

Open in new window

0
 
vcharlesAuthor Commented:
Thank You. I will try it and get back to you.
0
 
Fernando SotoCommented:
OK.
0
 
vcharlesAuthor Commented:
Hi,


Do I need to import something?

I received error message:

DataGridViewRowCollection is not  Queryable   make sure you are not missing a reference.......

 on line
Dim colorCells = From row As DataGridViewRow In C1Grid1.Rows _


But I don't get the same error for C1Grid1 on the other part of the code.


Thanks,

V.
0
 
Fernando SotoCommented:
Is C1Grid1 the DataGridView control?

Where you implemented the code, is the data table been filled with data and assigned to C1Grid1 so that it is not empty?
0
 
vcharlesAuthor Commented:
Hi,

I added system.linq to remove the error.
 
will get back to you shortly.

V.
0
 
Fernando SotoCommented:
Very good.
0
 
vcharlesAuthor Commented:
Hi,

It works Perfectly!

How do I highlight the rows with a perfect match?

Thank You!

Victor
0
 
vcharlesAuthor Commented:
Hi,

I will ask my last question in a new post.

Thanks again.

Victor
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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