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