How to use colors in Excel 2010 VBA code

If you want to use colors in your VBA macros e.g. to modify cell background color or to change the font color, then you have two options on how to specify the colors:

1. By choosing from one of the preset EXCEL colors (56 colors). You do this by specifying a color index.

2. You can set your own colors by using RGB (Red, Green and Blue) values.

Examples of using the preset colorindex property.
1. Set background color:

Worksheets(“Calendar”).Range(“A1:M1”).Interior.ColorIndex = 34

2. Set font color:

Worksheets(“Calendar”).Range(“A1:M1”).Font.ColorIndex = 34

3. The following VBA code demonstrate how to use colorindex property. It is output is the complete list of the color index palette. The image below is the output from that code.

If you want to try it, name a sheet in your workbook : “ColorIndex” and then paste the code in a new module and run it.

Sub ShowColorIndex()

Dim i As Integer, j As Integer

For i = 1 To 4

For j = 1 To 14

Worksheets(“ColorIndex”).Cells(j, (i – 1) * 2 + 1).Value = (i – 1) * 14 + j

Worksheets(“ColorIndex”).Cells(j, i * 2).Interior.ColorIndex = (i – 1) * 14 + j

Next j

Next i

End Sub

Examples of using the color property:

Set  background:

Worksheets(“Calendar”).Range(“A1:M1”).Interior.Color = RGB(218,225,130)

Set font  color:

Worksheets(“Calendar”).Range(“A1:M1”).Font.Color = RGB(218,225,130)

How do you figure out the right RGB color combination? Well you can experiment with the color palette. The following post will show you how.
How to create a custom font color or fill color in Excel 2010

How to create a custom font color or fill color in Excel 2010

To specify a custom font color or fill color in Excel 2010 follow these steps:

1. In the Home tab, Font group, click on the arrow next to the Fill Color icon. This will show the window with the theme colors.

2. At the bottom of the window, click on More Colors. The colors window will be displayed.

3. Click on the Custom tab.

4. Click anywhere in the colors palette to choose the color you want, then click on the vertical bar on the right side

5. The corresponding RGB color values will show under the color palette.

6. Click OK.

 

You can do the same thing for Font Color. In step 1 above, instead of using the Fill Color icon use the Font Color icon.

 

Conditional formatting in Excel 2007 , Top / Bottom rules

IF you have a range of data and you want to highlight, say, the top 10 values by giving them a unique fill color or font color, then follow these steps:

  1. In the Home tab, Styles group click Conditional formatting.
  2. Point to Top/Bottom Rules and click Top 10 Items.
  3. The Top 10 Items window will open.
  4. Specify the number of items to format (left) and the formatting options from the drop-down list on the right. If you are not satisfied with what is on the list, you can click Custom Format at the bottom and then set up your own.
  5. Your data will look like this.

How to split contents of a cell into adjacent columns

If you have a cell, or column, that contains some text and you want to distribute that text into the adjacent columns, then :

1.       Select the cell, range of cells within the same column or the entire column that you want to distribute its data.

2.       On the Data tab, Data Tools group, click Text to Columns. This will start the Convert Text to Columns Wizard .

3.       In the first step of the wizard specify your data type then click Next :

a.       “Delimited” means data fields are separated by special characters like commas. E.g ” Shakespeare,William “.

b.      “Fixed” means fields are aligned in equal size columns separated by spaces. E.g ” Shakespeare William “.

4.       In the second step of the wizard you specify the field delimiters, if you choose “Delimited” in the previous step, or specify the field widths, if you choose “Fixed” in the previous step. Click Next when you are done.

5.       The last step lets you specify the data format of each column, if it is different from the default set by Excel. Click Finish when you are done.

6.       The data will be distributed into a number of columns depending on the number of separators or spaces in the original column.

How to highlight duplicate values using conditional formatting

You can highlight duplicate values in a data range by using conditional formatting. Follow these steps:

  1. Select your data range.
  2. In the Home tab, Styles group click Conditional formatting.
  3. Point to Highlight Cells Rules.
  4. In the sub-menu to the right click Duplicate Values.
  5. The Duplicate Values window will open. Click OK.
  6. Any duplicate values in the range will be highlighted with the default color. You can change the color before clicking OK.

Applies to: Excel 2007, Excel 2010

How to hide and unhide worksheets in Excel 2007

If you have a workbook with multiple worksheets and you want to hide some worksheets, e.g. to protect data from accidental change, you can do that as follows:

  1. Select the worksheet(s) you want to hide.
  2. In the Home tab, Cells group click Format.
  3. Under Visibility, point to Hide & Unhide and then click Hide Sheet.

To display or unhide a worksheet:

  1. In the Home tab, Cells group click Format.
  2. Under Visibility, point to Hide & Unhide and then click Unhide Sheet.
  3. In the Unhide sheet box, double-click the name of the hidden sheet that you want to display.

Applies to: Excel 2007, Excel 2010

How to clear conditional formatting in Excel 2007

To clear conditional formatting from a range of cells follow these steps:

  1. Select the cells for which you want to clear conditional formatting.
  2. On the Home tab, Styles group, click Conditional formatting.
  3. Click Clear Rules.
  4. From the menu to the right Click Clear Rules from Selected Cells.

To clear conditional formatting from the whole worksheet (no selection is required):

  1. On the Home tab, Styles group, click Conditional formatting.
  2. Click Clear Rules.
  3. From the menu to the right Click Clear Rules from Entire Sheet.

AutoFit row heights in Excel 2007

If you have a number of rows in your sheet and each one has a different size of data. For example one row may have a wrapped text or a text with big font size that does not match with  the current row height, then you can adjust the row height.

To adjust the height of each row to fit  the entered data:

  1. Select the rows to be adjusted.
  2. In the Home tab, Cells group, click Format.
  3. From the Format menu Click AutoFit Row Height.

Alternatively you can do the same by following these steps:

  1. Select the rows to be adjusted.
  2. Point to the lower border of anyone of the selected rows (at the left of the row where the row number is). The mouse pointer should change to a cross with vertical arrows at the top and bottom.
  3. Double click on the row border.

Before:

After:

AutoFit Column widths in Excel 2007

You may have a number of columns in your sheet and each one has a different size of data. For example one column may have only a 2 digit sequence number but another column has a long text.

To adjust the size of each column to fit  the entered data:

  1. Select the columns to be adjusted.
  2. In the Home tab, Cells group, click Format.
  3. From the Format menu Click AutoFit Column Width.

Alternatively you can do the same by following these steps:

  1. Select the columns to be adjusted.
  2. Point to the right border of anyone of the selected columns (at the top of the column where the column label is). The mouse pointer should change to a cross with horizontal arrows to the left and right.
  3. Double click on the column border.

Before:

After:

How to some times greater than 24 hours in Excel 2007

To sum times you usually use Excel built-in function SUM. If you have three cells A1, A2 and A3 formatted as time (hh:mm), and you have :

A1 = 02:30

A2 = 10:15

A3 has the formula: =SUM(A1:A2)

then A3 will have the correct values of 12:45.

However if your total times are greater than 24 hours then things get tricky.

If you have :

A1 = 15:30 and A2 = 10:15 then A3 will have 01:45 instead of 25:45. I.e. any hours beyond 24 hours are dropped.

To retain the hours beyond 24, you have two alternatives:

1. If you want to convert the hours beyond 24 into days then use this cell format : dd:hh:mm. The total in the previous example will be 01:01:45 ( 1day , 1 hour and 45 minutes)

2. If you want to keep the hours beyond 24 as is then use this cell format :[h]:mm. The total in the previous example will be 25:45.

In both cases the formula will remain as it is. Only the cell format differs.

See another example below:

More posts on time:

http://www.exceldigest.com/myblog/2009/02/08/how-to-add-or-sum-times/
http://www.exceldigest.com/myblog/2008/12/04/how-to-create-a-custom-time-format/