Excel 2010 VBA Lesson 21: Formatting Colors

 [Lesson 20]<<[Table of Contents]>>[Lesson 22]

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

Alright, let’s create a program that can format random font and background colors using a randomizing process. Colors can be assigned using a number of methods in Excel VBA 2010, 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

For example

cells(1,1).Font.Color=RGB(255,255,0) 

will change the font color to yellow

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 are shown in the following table:

ColorRGB Code
 (0,0,0)
 (255,0,0)
 (255,255,0)
 (255,165,0)
 (0,0,255)
 (0,128,0)
 (128,0,128)
 (0,255,255)
 (128,0,0)
 (128,128,128)
 (255,0,255)
 (220,20,60)



Example 21.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 formats 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

vba2010_figure21.1

Figure 21.1



Example 21.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()
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

vba2010_figure21.2

Figure 21.2



 [Lesson 20]<<[Table of Contents]>>[Lesson 22]

Excel VBA Lesson 16 : Formatting Font and Background Colors

<<Lesson 15>> [Contents] <<Lesson 17>>

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

Alright, let’s create a program that can format random font and background colors using a randomizing 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 are shown in the following chart,

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 16.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 16.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 15>> [Contents] <<Lesson 17>>

Excel VBA Lesson 9: Formatting Functions

<<Lesson 8>> [Contents] <<Lesson 10>>

We need to format the output of an Excel VBA code so the users can understand it easily. In the previous lesson, we have learned a few basic formatting functions like Int, Fix and Round. However,  in order to have better control of the output format, we can the format function in Excel VBA. The Format function is a very useful function as it can display the numeric values in various forms. There are two types of Format function, one of them is the built-in or predefined format while another one can be defined by the users.



9.1 Predefined Format function

The syntax  of the predefined Format function is

Format (n, “style argument”)

where n is a number and the list of style arguments are listed in Table 9.1

Excel VBATable 9.1

Example 9.1

Private Sub CommandButton1_Click()
Cells(1, 1) = Format(8972.234, “General Number”)
Cells(2, 1) = Format(8972.2, “Fixed”)
Cells(3, 1) = Format(6648972.265, “Standard”)
Cells(4, 1) = Format(6648972.265, “Currency”)
Cells(5, 1) = Format(0.56324, “Percent”)
End Sub

The Output is shown in Figure 9.1

Excel VBA

                       Figure 9.1



9.2 user-defined Format function

The syntax of the user-defined Format function is

Format (n, “user’s format”)

Although it is known as user-defined format, we still need to follow certain formatting styles. Examples of user-defined formatting style are listed in Table 9.2

vba_table9.2

Table 9.2

Example 9.2

Private Sub CommandButton1_Click()

Cells(1, 1) = Format(781234.57, “0”)
Cells(2, 1) = Format(781234.57, “0.0”)
Cells(3, 1) = Format(781234.576, “0.00”)
Cells(4, 1) = Format(781234.576, “#,##0.00”)
Cells(5, 1) = Format(781234.576, “$#,##0.00”)
Cells(6, 1) = Format(0.576, “0%”)
Cells(7, 1) = Format(0.5768, “0.00%”)

End Sub

The output is shown in Figure 9.2

vba_figure9.2 Figure 9.2

<<Lesson 8>> [Contents] <<Lesson 10>>