Excel VBA Lesson 12 : Formatting Font and Background Colors

Google+Google GmailYahoo MailSina WeiboTumblrPinterestShare

<<Lesson 11>> [Contents] <<Lesson 13>>


In this Lesson, we will explore how to write Excel VBA code that formats the color of a MS Excel spreadsheet. Using  Excel VBA code, we can change the font color as well as the the background color of each cell effortlessly.

Alright, let’s create a program that can format random font and background colors using a randomize process. Colors can be assigned using a number of methods in Excel 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 syntax to set the font color is

cells(i,j).Font.Color=RGB(x,y,x)

where x ,y , z are any numbers between 1 and 255

The syntax 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


Some RGB Color Codes

Color RGB Code
(0,0,0)
(255,0,0)
(255,255,0)
(255,165,0)
(0,0,255)
(0,128,0)
(128,0,128)

Example 12.1

In this example, clicking the command button changes the background colors from Cells(1,1) to Cells(7,1) according to the specified RGB color codes. It also format the font colors from Cells(1,2) to cells(7,2) using specified RGB color codes.

The code

Private Sub CommandButton1_Click()
Dim i As Integer

‘To fill the cells with colors using RGB codes
Cells(1, 1).Interior.Color = RGB(0, 0, 0)
Cells(2, 1).Interior.Color = RGB(255, 0, 0)
Cells(3, 1).Interior.Color = RGB(255, 255, 0)
Cells(4, 1).Interior.Color = RGB(255, 165, 0)
Cells(5, 1).Interior.Color = RGB(0, 0, 255)
Cells(6, 1).Interior.Color = RGB(0, 128, 0)
Cells(7, 1).Interior.Color = RGB(128, 0, 128)
‘To format font color with RGB codes
For i = 1 To 7
Cells(i, 2).Value = “Font Color”
Next
Cells(1, 2).Font.Color = RGB(0, 0, 0)
Cells(2, 2).Font.Color = RGB(255, 0, 0)
Cells(3, 2).Font.Color = RGB(255, 255, 0)
Cells(4, 2).Font.Color = RGB(255, 165, 0)
Cells(5, 2).Font.Color = RGB(0, 0, 255)
Cells(6, 2).Font.Color = RGB(0, 128, 0)
Cells(7, 2).Font.Color = RGB(128, 0, 128)

End Sub

The Output

Excel VBA

Example 12.2

In this 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.Rnd is a random number between 0 and 1, therefore 255* Rnd will produce a number between 0 and 255  and 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.
For example;Rnd=0.229
255*Rnd=58.395
Int(58.395)=58

The code

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

The Output

<<Lesson 11>> [Contents] <<Lesson 13>>

Google+Google GmailYahoo MailSina WeiboTumblrPinterestShare

Please write your comment and share your ideas here :

Your email address will not be published. Required fields are marked *.

*