Breaking News

Friday, October 5, 2012

4 VBA Course : Colors


Let's start by assigning a color to the text in A1.

After adding Font., we get this result :
color - colors

There are two different ways that we can set the color : ColorIndex, which has 56 colors, or Color which makes it possible to use any color at all.

ColorIndex :

Here you can see the 56 colors that are available through ColorIndex :
colorindex - colors
To set the color of our text to one of these 56, we should write :
 
Sub example()
   'Text color for A1 : green (Color num. 10)
   Range("A1").Font.ColorIndex = 10
End Sub

This code will give us the following result :
test2 - colors

For versions of Excel lower than 2007 : using ColorIndex is preferable to using Color.

Color :
Here is a similar example in which we use Color :
 
Sub example()
   'Text color for A1 : RGB(50, 200, 100)
   Range("A1").Font.Color = RGB(50, 200, 100)
End Sub

In this case, the color is : RGB(50, 200, 100).
RGB stands for Red-Green-Blue, and the numerical values go from 0 to 255 for each color.
A few examples of colors so that you can understand this better :
  • RGB(0, 0, 0) : black
  • RGB(255, 255, 255) : white
  • RGB(255, 0, 0) : red
  • RGB(0, 255, 0) : green
  • RGB(0, 0, 255) : blue
Luckily, there are lots of easy ways to find the RGB values for colors. Here's one (click on the image) :

colors
Choose the color that you want from this utility and just copy the three values into the RGB(red_value, green_value, blue_value).
So to change our text color to the violet above, we should use the following code :
 
Sub example()
   'Text color for A1 : RGB(192, 24, 255)
   Range("A1").Font.Color = RGB(192, 24, 255)
End Sub

This code will produce the following result :
test - colors

For versions of Excel lower than 2007 : the number of colors is limited (the closest available color to the RGB values will be used).
Add colored borders :
We will now create a macro that adds a border to the active cell using ActiveCell.

The border will be heavy and red :
 
Sub example()
   'Border weight
   ActiveCell.Borders.Weight = 4
   'Border color : red
   ActiveCell.Borders.Color = RGB(255, 0, 0)
End Sub

Result :
border - colors
To apply this effect to many cells at once, we can use the Selection command:
 
Sub example()
   'Border weight
   Selection.Borders.Weight = 4
   'Border color : red
   Selection.Borders.Color = RGB(255, 0, 0)
End Sub

Add background color to the selected cells :
 
Sub example()
   'Add background color to the selected cells
   Selection.Interior.Color = RGB(174, 240, 194)
End Sub

Result :
background - colors

Add color to the tab for a worksheet :
 
Sub example()
   'Add color to the tab for "Sheet1"
   Sheets("Sheet1").Tab.Color = RGB(255, 0, 0)
End Sub

Result :
tab - colors

No comments:

Post a Comment

Designed By Published.. Blogger Templates