Sunday, May 13, 2007

Changing Cell Color with VBA in Excel


By Guy Lecky-Thompson:


Introduction


This article covers ways to change cells and cell properties in Excel, using Visual Basic for Applications, or VBA. VBA is the built-in macro programming langauge used by all Microsoft Office applications, and allows the programmer to change the properties according to the document model provided by the underlying application.


Sometimes it is useful to change the appearance of a cell based on it’s contents. For example:



  • Teacher’s spreadsheets - low grades in red;

  • Web site statistics - climbers in green, losers in red;

  • Names in different colors, etc. etc.

In order to do this we need to isolate those cells that we wish to hilight, the criteria upon which we wish to make the decision, and the actual hilighting feature we wish to employ. In Excel, VBA provides a way to change many properties, including:



  • Font (Text) Color;

  • Font (Text) Size;

  • Cell background color (shading);

  • Cell borders, etc. etc.

For the purpose of this discussion, we shall assume that we have a spreadsheet, with a column titled ’Average’, and that we want anything less than 50.0 to be hilighted by a red background. The sheet shall be called ’Totals’.


(There is addition information in the article Getting Started with VBA for MS Excel that covers how to enter code, and prepare macros and Visual Basic programs, for the uniniated...)


Referencing the Cells


Excel works with cells in ranges. A range can be one or more cells. These cells are contained in rows and columns in a sheet. Each sheet has a property, ’Cells’, which allows us to reference such a range.


At it’s smallest granularity, we might use this propery to reference a single cell:


Worksheets("Totals").Cells(1,1)

The above refers to a single cell, at Row 1, Column 1 - it is the equivalent to A1 on a spreadsheet. Row 1, Column 2, (1,2) would be cell B1. Row 2, Column 3 would be cell C2, and so on.


We can also refer to an entire column:


Worksheets("Totals").Columns(1)

The above refers to all of Column A. So, we have enough information to prepare the code that determines which column we are interested in; this was the first step we isolated in the Introduction.


We need to look at all the column headers, and note down which column has the word ’Average’ in it. This can be done as follows:


Function FindColumn(szName) As Integer
nFoundColumn = 0
For nColumn = 1 To Worksheets("Totals").Columns.Count
If StrComp(Worksheets("Totals").Cells(1, nColumn), szName) = 0 Then
nFoundColumn = nColumn
End If
Next nColumn
FindColumn = nFoundColumn
End Function

In this snippet, the For loop (code between the For... and Next... statements) allows us to move through the columns, nColumn by nColumn. We use StrComp to evaluate the contents against the szName parameter fed into the Function.


The function returns (using the line FindColumn = nFoundColumn) the index of the column containing the value szName.


Scrolling Through the Dataset


Once we have found the column, we can proceed to loop through the rows (starting at row 2, row 1 containing the heading), evaluating the contents of each cell. To look up the column, we use the following call to our FindColumn function:


nCol = FindColumn("Average")

If this call returns 0, we know that the column does not exist, and we need go no further. From our previous For code snippet above, we can construct a similar loop for the rows:


For nRow = 0 To Worksheets("Totals").Columns(nCol).Rows.Count
Rem Do Work Here
Next nRow

At each iteration of the loop, we need to evaluate the cell, and decide whether it is lower than the threshold (in this case, 50). However, given that the Count might include cells that are empty (rather than set to 0), we might not want to include them. This is especially true since Excel will run through all 65,000 rows, and evaluate each one. If we do not either:



  • Tell Excel to stop at the last Row;

  • Ignore empty cells.

There are therefore two possibilities - test for a known ’stop’ value in a cell, or ignore empty cells. The advantage of having a stop value is that empty cells will be chosen, and hilighted, otherwise they need to be set to 0 in order to be chosen.


For the sake of simplicity, we will just ignore empty cells:


For nRow = 0 To Worksheets("Totals").Columns(nCol).Rows.Count
If Not IsEmpty(Worksheets("Totals").Cells(nRow, nCol)) Then
If Worksheets("Totals").Cells(nRow, nCol) 50.0 Then
Rem Hilight cell
End If
End If
Next nRow

The above uses the IsEmpty function to ascertain whether the cell contains any data. The double If (nested If) statement is used for clarity, a Boolean operator could have been used to combine the two statements.


Hilighting the Cell


Finally, we need to hilight the chosen cell:


Worksheets("Totals").Cells(nRow, nCol).Interior.ColorIndex = 3

The Interior property references the color and style of the shading. It uses an index, rather than a real color, and a collection of constants to set the shading style. These are out of scope for this article, but can be found in the Visual Basic for Excel online help files.


Summary


The above is just one way to achieve the goal, and is presented here for education, and not as the most elegant solution. In particular, the bounds checking for the data set, and hilighting code could be changed to allow for more flexibility / user friendliness. Fee free to post your contribution at the end of this page.



Changing Cell Color w/VBA in Excel: How to conditionally change cell properties based on contents.


at