Home » VB.NetRSS

DataGridView - Change Cell Color if Particular Value

Hi,

I have a bunch of columns in a datagrid view (date, transaction type, amount).

If the amount in a row is '150.00', I want to make that cell red.

I made a Private Sub and run this after the datagridview is filled.

Private Sub ColorCells()

    Dim ColumnCount As Integer
    ColumnCount = dgvLedger.ColumnCount - 1
    Dim ColumnSelected As Integer = 5
    Dim ColumnLoop As Integer
    ' MessageBox.Show("" & ColumnCount & "")

    Try
      For ColumnLoop = 0 To ColumnCount



        Dim CountInt32 As Int32
        Dim CellValueString As String
        Try
          For CountInt32 = 0 To dgvLedger.Rows.Count - 1
            CellValueString = dgvLedger.Rows(CountInt32).Cells(ColumnSelected).Value.ToString
            'MessageBox.Show("" & CellValueString & "")

            dgvLedger.Columns(ColumnSelected).DefaultCellStyle.BackColor = Color.Lime
            dgvLedger.Columns(ColumnSelected).DefaultCellStyle.ForeColor = Color.Black
            MessageBox.Show(CellValueString)


            If CellValueString = "150.00" Then
              dgvLedger.Rows(CountInt32).Cells(ColumnSelected).Style.BackColor = Color.Red
            End If
          Next CountInt32
        Catch ex As Exception
          '   pErrorMessageString = ex.Message
        End Try

        ColumnSelected = ColumnSelected + 1

      Next ColumnLoop

    Catch ex As Exception
      'Fail

    End Try


  End Sub


However it seems to have two bugs. 

When it is first run the first time when the form is loaded, it doesn't put the rows with 150.00 in it in red.

If I have a button on the form and set it to run this Private Sub, then it runs through it again and will color the ones in 150 as red.

If I resort the datagridview by clicking on a column, they all return to green again, and it looses the formatting.

Any way to resolve these two bugs?

 

Thanks.

 

 

 

 

6 Answers Found

 

Answer 1

The datagridview  can be tricky with formatting at times.  i have had some trouble in my last app as well.  had to put a few band-aids on it.  and i found out today that i will need to go back and redo much of the use of the datagridview to correct the issues.

aside from my issues, what i suggest is this:

try setting the defaultcellstyle of your column before the loop, and only set one color.  or you may choose to set individual cell  backcolors as your loop.  check first for you 150 value, if found then set the red color  and then use "continue for" to move past to the next row.

as far as it not setting initially, might depend on when you are calling the code.  are you calling it from the form load event, and is the grid already populated before you try to set the colors?

the sort may be an issue because changes to the list structure of the rows may cause resetting of the formats.  This is similar to the issue i have been having with adding columns  to a datagridview.  for some reason it loses all formatting.  I am pretty sure my issue is because of hosting the datagridview in a panel but have not tested it outside yet.  Not sure the datagridview is as good as it should be.  seems to do best with less formatting.

What i used to use before i switched completely to the datagridview for my problem app is a tool called spreadsheetgear for .net.  Very good tool and very reliable for formatting, etc...  it just plain works.  i switched because i wanted to do some different things with a new version of my app, but i probably should have stayed how i had it and just added on the extras.  if i don't get any results after moving the grid out of the panel i will move back to spreadsheetgear.  if for some reason you are not able to get it worked out with the grid, you can get a free version of the tool when you register visual studio express editions and i beleve sql server express as well, in your registration benefits portal.

 

Answer 2

Thanks for your reply Geoff.

Spent quite a few more hours.

As mentioned if you click a column header in a datagridview, you will loose formating.

So I was thinking maybe when the form loaded, if anything altered the form after I did my colorcell function, it would reset it.

What I did is set a message box at the start of the function, and end, and then also re-ran the colorcell function at the end of the form_load event.

Anyhow, none of the above solved it.

Maybe having it in a group box on a tab affects it?

However when I am on the tab with the groupbox and I add an insert data function and click the add button, it refreshes the data, and re-runs the color  function and it colors the cells correctly.

Any other pointers you can think of?

 

Answer 3

I can't say what is the best way.  in my current app, i have about 4 forms that are opened at one time to access different table data.  each has a datagridview.  they all refresh fine after adding a new record or updating one.  note that i am updating the data from an editing form and just send the new record object to the datagrdivew datasource when done.  the last form i have, the datagridview  will not refresh after updating a record in the source.  there is no difference between this one and the others except for the data.  there is no formatting or anything extra added.  just columns, rows and data.  so why does this one not refresh without a specific call to the refresh method?  good question.  but i have a suspiscion that it has to do with how many datagridview are contained inside one "thing".  for instance i have another app with two datagridview in a panel.  i also have another small panel inside this same pane.  and inside the small panel i have another datagridview.  i did it this way to try and get around some default sizing behavior of child forms inside a mdiparent.  the second datagridview will lost all formatting, as i think i mentioned in previous posts, but the small panel datagridview, if you tab through any of the cells, and then exit, it makes one of the other datagridviews, that gets focused, show an outline around the current selected cell.  if you open the small panel again without tabbing and exit, it will then reset the other datagridview cell  to not have an outline.

i am really starting to think there is some sort of issue with confusion between the number of grids inside of one single object.  whether it is a mdiparent, panel, groupbox, etc...  i think if you can keep some seperation between them it is the best way.  of course that is not easy always.  so possibly we are stuck at the moment.  if i keep having too much trouble i will start replacing my datagridview with worksheets using SpreadsheetGear for .NET.  i have yet to see any of the same issue with formatting or refreshing.  The issue with the datagridview to me is a ridiculous one that should have gotten much more attention.  they are moving to wpf for new stuff now, and maybe it will be more reliable in these areas, but for now it is too slow to deal with for me.  if it can eliminate some issues like this, it might be worth it but for now just have to find some work arounds till things improve all around.

not sure any of this helps you or not.  my suggestion is to be sure you are seperating as much as possible and try not to contain the datagridvieds in other controls.

hope this helps

 

Answer 4

Well I think you hit the nail on the head.

This deserves more attention - and it has not been solved. Wonder many others don't complain.

I have a MDI parent, and the datagridview  is one of 5 or so that appear on a mdichild, however there is only one per tab.

So obviously it confuses its brain out....

 

Answer 5

I have not much hand experience in VB Language. Following code is in C#. You convert it in VB. It will  work for you.

       private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)

        {

 

            try

            {

                int intDecimal = 20000;

                if (dataGridView1.Columns[e.ColumnIndex].Index.Equals(7))//.Name.Equals("BasicSalary"))

                {

                    if (e.Value != null)

                    {

 

                        if ((decimal)(e.Value) < System.Convert.ToDecimal(intDecimal))

                        {

                            e.CellStyle.BackColor = Color.Red;

                            e.CellStyle.SelectionBackColor = Color.DarkRed;

                        }

                    }

                }

            }

            catch (SqlException ex)

            {

 

            }

        }

 

 

Answer 6

I have not much hand experience in VB Language. Following code is in C#. You convert it in VB. It will  work for you.

 

       private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)

        {

 

            try

            {

                int intDecimal = 20000;

                if (dataGridView1.Columns[e.ColumnIndex].Index.Equals(7))//.Name.Equals("BasicSalary"))

                {

                    if (e.Value != null)

                    {

 

                        if ((decimal)(e.Value) < System.Convert.ToDecimal(intDecimal))

                        {

                            e.CellStyle.BackColor = Color.Red;

                            e.CellStyle.SelectionBackColor = Color.DarkRed;

                        }

                    }

                }

            }

            catch (SqlException ex)

            {

 

            }

        }

 

 

Just Replace

if ((decimal)(e.Value) < System.Convert.ToDecimal(intDecimal))

with

if (System.Convert.ToDecimal(e.Value) < System.Convert.ToDecimal(intDecimal))

And it works perfectly

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter