Excel VBA Classic Excel VBA 2010 Excel VBA 365 Excel VBA Examples About Us

Lesson 16: Font and Background Color

In this lesson, we shall explore how to create Excel VBA that can format the color of an Excel spreadsheet. Using VBA codes, we can change the font color and the the background color of each cell .We shall also learn to write an Excel VBA program that can create random font and background colors. Colors can be assigned using a number of methods in Excel VBA, but we shall focus on the RGB function. The  RGB function has three numbers corresponding to the red, green and blue components. The range of values of the three numbers is from 0 to 255. A mixture of the three primary colors will produce different colors.

The syntax to set the font color is


where x ,y , z can be any number between 1 and 255

For example


will change the font color to yellow

The syntax to set the cell's background color is


where x ,y , z can be any number between 1 and 255

In the following example, the font color in cells(1,1) and background color in cells(2,1) are changing for every click of the command button due to the randomized process.

Private Sub CommandButton1_Click()
 Randomize Timer
 Dim i, j, k As Integer
 i = Int(255 * Rnd) + 1
 j = Int(255 * Rnd) + 1
 k = Int(255 * Rnd) + 1
 Cells(1, 1).Font.Color = RGB(i, j, k)
 Cells(2, 1).Interior.Color = RGB(j, k, i)
End Sub


Rnd is a random number between 0 and 1

255* Rnd will produce a number between 0 and 255

Int(255*Rnd) will produce integers that take the values from 0 to 254

So we need to add 1 to get random integers from 0 to 255.


Figure 16.1 The Output

❮ Previous Lesson Next Lesson ❯

Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved   [Privacy Policy]

Contact: Facebook Page