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.
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
' Fill the range with random numbers between
' -60 and 50.
Dim rng As Range
Set rng = Range("A1", "A40")
' 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
Sub SetupRandomData(rng As Range)
rng.Formula = "=RANDBETWEEN(-60, 50)"
Step 5: Press F5 to run the application.
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.