Solved

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

Posted on 2014-03-07
13
432 Views
Last Modified: 2014-03-08
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
Comment
Question by:vcharles
  • 7
  • 6
13 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39913761
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
 

Author Comment

by:vcharles
ID: 39913817
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39914171
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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

Author Comment

by:vcharles
ID: 39914263
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
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39914703
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
 

Author Comment

by:vcharles
ID: 39914778
Thank You. I will try it and get back to you.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39914780
OK.
0
 

Author Comment

by:vcharles
ID: 39914817
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39914829
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
 

Author Comment

by:vcharles
ID: 39914832
Hi,

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

V.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39914834
Very good.
0
 

Author Comment

by:vcharles
ID: 39914863
Hi,

It works Perfectly!

How do I highlight the rows with a perfect match?

Thank You!

Victor
0
 

Author Comment

by:vcharles
ID: 39914883
Hi,

I will ask my last question in a new post.

Thanks again.

Victor
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

839 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