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.
  • 1726

Lets create the Application

Step 1: Start Microsoft Excel 2010

01.jpg

02.jpg

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

macroscreen.jpg

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

vbscreen.jpg
 

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

macrorun.jpg

Step 7: You output will shows on Microsoft Excel 2010

outputscreen.jpg

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.

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.