VBA

Today we will explore how to create VBA that can format the color of a MS Excel spreadsheet. Using Visual Basic codes, we can actually change the font color as well as the the background color of each cell effortlessly. Alright, I am going to creating a program that can create random font and background colors using a randomize process. Colors can be assigned using a number of methods in VBA, but it is easier to use 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 format to set the font color is

cells(i,j).Font.Color=RGB(x,y,x), where x ,y , z can be any number between 1 and 255

For example

cells(1,1).Font.Color=RGB(255,255,0) will change the font color to yellow

The format to set the cell's background color is

cells(i,j).Interior.Color=RGB(x,y,x), 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

Explanation:
　
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.
exampe;
　
Rnd=0.229
255*Rnd=58.395
Int(58.395)=58
　
　
　

Chapter 8 : Font and Background Color