Change colors of ranges in excel using VBA

This article is all about changing the color of the cell to indicate if the value of the cell is above the average then what kind of color it will display and if the value in the cell is below the average then what kind of color it will display.
  • 2864

Lets create the Application

Step 1: Start Microsoft Excel 2010



Step 2: Now press Alt + F11 to open Microsoft Visual Basic for Applications


Step 3: Now choose Sheet1 to write your code from the project window


Step 4: Write the following code in code window

Sub TestAboveAverage()
 ' Fill the range with random numbers between
    ' -60 and 50.
    Dim rng As Range
    Set rng = Range("A1", "A40")
    SetupRandomData rng
    ' Create a conditional format for values above average.
    Dim aa As AboveAverage
    Set aa = rng.FormatConditions.AddAboveAverage
    aa.AboveBelow = xlAboveAverage
    aa.Font.Bold = True
    aa.Font.Color = vbGreen
    ' Create a conditional format for values below average.
    Dim ba As AboveAverage
    Set ba = rng.FormatConditions.AddAboveAverage
    ba.AboveBelow = xlBelowAverage
    ba.Font.Color = vbRed
End Sub
Sub SetupRandomData(rng As Range)
    rng.Formula = "=RANDBETWEEN(-60, 50)"
End Sub

Step 5: Press F5 to run the application.

Step 6: Macros window will open, here check the macro name and hit the run button


Step 7: You output will shows on Microsoft Excel 2010


In the above output it is clear that the value that are above the average are of green color and the value that are below the average are of red color. Here we are trying to let you know the procedure to indicate the values by the different color according to our business logic.
I Hope you would like it.


More Articles

© 2020 DotNetHeaven. All rights reserved.