Change Cell Colors

Change the Colors of Cells Using Excel Visual Basic

Interior (Background) Color:

The general format is:

Range("Cell_Range").Interior.Color = RGB(R, G, B)

OR:

Range("Cell_Range").Interior.ColorIndex = index_ID

Font Color:

The general format is:

Range("Cell_Range").Font.Color = RGB(R, G, B)

OR

Range("Cell_Range").Font.ColorIndex = index_ID

 

Cell Border Color:

The general format is:

Range("Cell_Range").Borders.Color = RGB(R, G, B)

OR

Range("Cell_Range").Borders.ColorIndex = 49

OR

Range("Cell_Range").Borders(Which_Border).Color =RGB(R, G, B)

 

Cell_Range indicates Which cells you want to be colored. (R,G,B) indicates how much (R)ed, (G)reen, and (B)lue you want to include in the color. Each RGB value has a maximum value of 255 and a minimum of 0. RGB(255,0,0) uses maximum red with no other colors, making the color red.  RGB(0,255,0) uses no red or blue with a maximum green, making the color green. RGB(150,20,150) makes it this color.

index_ID is a pre-determined color that can be determined from this list: Color Options

Which_border, for the border colors, indicates the desired border to change the color of

 

Example 1: Change the Interior Color of a Cell

Private Sub CommandButton1_Click()
     Range("A1:A10").Interior.Color = RGB(255, 0, 0)
    
     Range("B1:B10").Interior.ColorIndex = 49
 End Sub

One line uses RGB values to set part of column “A” to a background of red. The other uses ColorIndex to change it to a blue color. It looks like the image below when executed.

Example1

Example 2: Change the Font Color of a Cell

Private Sub CommandButton1_Click()
     Range("A1:A10").Font.Color = RGB(255, 0, 0)
    
     Range("B1:B10").Font.ColorIndex = 49
 End Sub

The first line changes the font in part of column A to a red color. The second line uses ColorIndex to change the font in part of column B to a dark blue color.

Example2.png

Example 3: Change the Border Color of a Cell

Private Sub CommandButton1_Click()
     Range("A1:A10").Borders.Color = RGB(255, 0, 0)
    
     Range("B1:B10").Borders.ColorIndex = 49
    
     Range("D1:D10").Borders(xlLeft).Color = RGB(0, 0, 255)
     Range("D1:D10").Borders(xlBottom).Color = RGB(255, 0, 255)
    
     Range("E1:E10").Borders(xlRight).ColorIndex = 4
     Range("E1:E10").Borders(xlTop).ColorIndex = 51
 End Sub

The first line changes all the borders of the A column range to RGB(255,0,0), which is red. The second line uses colorIndex to change all the borders in the B column range to a dark blue. Lines 4 through 8 show specific borders being colored.

Example3.png

Example 4: Background, Font, and Border Color Change

Private Sub CommandButton1_Click()
     Range("A1:A10").Interior.Color = RGB(0, 0, 128)
     Range("A1:A10").Font.Color = RGB(255, 255, 255)
     Range("A1:A10").Borders.ColorIndex = 53
 End Sub

Example4

 

Some Possible Colors Choices: Color Options

Color Index_ID R G B
1 0 0 0
2 255 255 255
3 255 0 0
4 0 255 0
5 0 0 255
6 255 255 0
7 255 0 255
8 0 255 255
9 128 0 0
10 0 128 0
11 0 0 128
12 128 128 0
13 128 0 128
14 0 128 128
15 192 192 192
16 128 128 128
17 153 153 255
18 153 51 102
19 255 255 204
20 204 255 255
21 102 0 102
22 255 128 128
23 0 102 204
24 204 204 255
25 0 0 128
26 255 0 255
27 255 255 0
28 0 255 255
29 128 0 128
30 128 0 0
31 0 128 128
32 0 0 255
33 0 204 255
34 204 255 255
35 204 255 204
36 255 255 153
37 153 204 255
38 255 153 204
39 204 153 255
40 255 204 153
41 51 102 255
42 51 204 204
43 153 204 0
44 255 204 0
45 255 153 0
46 255 102 0
47 102 102 153
48 150 150 150
49 0 51 102
50 51 153 102
51 0 51 0
52 51 51 0
53 153 51 0
54 153 51 102
55 51 51 153
56 51 51 51

 

Excel VBA Resources

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: