locked
Change the colors of cells RRS feed

  • Question

  • Hi, I have a column in my excel sheet. And i have some numbers registered in it.Numbers are float like  : 100,59.05,.....

    I want a code in VBA which does thisfor me....: if numbers are like this 100,50,20,...(Decreaseing) the cells' color sould be green,else if the numbers' value is increasing the cells' color should be red.

    would you help me with some lines of code to slve this problem.

    Any help is appreciated.

    • Edited by MaryamSohrab Wednesday, September 21, 2011 6:55 AM
    Wednesday, September 21, 2011 6:47 AM

Answers

  • Hello Maryam,

    This code loops through every cell in column A. Cell A1's colour is not changed. If the cell's value is greater than the value of the previous cell, it is coloured red. If the value is lower, it is coloured green. If the value is the same as the previous one, the same colour is used. Two variables are used to keep track of both the colour and value.

    If your numbers are in a different column, just change all the A's to match. Change "main" to match the name of your worksheet.

    Sub Colour()
        'Find last full row in column A. Just change the A to the column your numbers are in
        lastRow = Worksheets("main").Cells(Rows.Count, "A").End(xlUp).Row
        
        Dim previousValue 'Stores previous value
        previousValue = Worksheets("main").Range("A1").Value
        Dim previousColour 'Stores previous colour
        
        For Each c In Worksheets("main").Range("A2:A" & lastRow).Cells
            If c.Value = previousValue Then 'If value doesn't change, use the same colour
                c.Interior.Color = previousColour
                previousValue = c.Value
            ElseIf c.Value > previousValue Then 'If value goes up, colour cell red
                previousColour = RGB(255, 0, 0)
                previousValue = c.Value
                c.Interior.Color = previousColour
            Else                              'Else (value has gone down) colour cell green
                previousColour = RGB(0, 255, 0)
                previousValue = c.Value
                c.Interior.Color = previousColour
            End If
        Next
    End Sub
    
    


    I tested this code in Excel 2010, but it should work on older versions too. Result of execution:

    I hope this helps you.

    Kind regards,


    James Finch MCDST


    • Edited by Woohoooo Wednesday, September 21, 2011 2:18 PM
    • Marked as answer by danishani Tuesday, January 31, 2012 5:28 PM
    Wednesday, September 21, 2011 2:07 PM

All replies

  • Hello Maryam,

    This code loops through every cell in column A. Cell A1's colour is not changed. If the cell's value is greater than the value of the previous cell, it is coloured red. If the value is lower, it is coloured green. If the value is the same as the previous one, the same colour is used. Two variables are used to keep track of both the colour and value.

    If your numbers are in a different column, just change all the A's to match. Change "main" to match the name of your worksheet.

    Sub Colour()
        'Find last full row in column A. Just change the A to the column your numbers are in
        lastRow = Worksheets("main").Cells(Rows.Count, "A").End(xlUp).Row
        
        Dim previousValue 'Stores previous value
        previousValue = Worksheets("main").Range("A1").Value
        Dim previousColour 'Stores previous colour
        
        For Each c In Worksheets("main").Range("A2:A" & lastRow).Cells
            If c.Value = previousValue Then 'If value doesn't change, use the same colour
                c.Interior.Color = previousColour
                previousValue = c.Value
            ElseIf c.Value > previousValue Then 'If value goes up, colour cell red
                previousColour = RGB(255, 0, 0)
                previousValue = c.Value
                c.Interior.Color = previousColour
            Else                              'Else (value has gone down) colour cell green
                previousColour = RGB(0, 255, 0)
                previousValue = c.Value
                c.Interior.Color = previousColour
            End If
        Next
    End Sub
    
    


    I tested this code in Excel 2010, but it should work on older versions too. Result of execution:

    I hope this helps you.

    Kind regards,


    James Finch MCDST


    • Edited by Woohoooo Wednesday, September 21, 2011 2:18 PM
    • Marked as answer by danishani Tuesday, January 31, 2012 5:28 PM
    Wednesday, September 21, 2011 2:07 PM
  • Thanks a lot. It worked ....

    I love  you

    I am so happy now....

    Thursday, September 22, 2011 7:41 AM
  • Glad it helped. :)
    James Finch MCDST
    Thursday, September 22, 2011 8:41 AM