A reader asked for help figuring out how to change the background color of a cell in an Excel spreadsheet based on the content of the cell. Initially, I thought it would be dead easy, but there were some things I didn't think about.
- The reader wanted to compare the current value of the cell with the previous value. That meant that the previous value had to be stored somewhere.
- Changing the background color requires the use of some "magic number" constants. They're different in Excel.
- Event code based on a change to the spreadsheet will create an infinite loop if the code doesn't have a way to prevent it.
To simplify the example, the code here only tests the value of a specific cell - B2 - and sets the background of that cell to a different color depending on whether the new content of B2 is less than, equal to, or greater than the previous content.
Comparing the current value of the cell with the previous value
When the user enters a new value in cell B2, the old value is gone so the old value has to be stored somewhere. The easiest way to do this is to save the value in some remote part of the worksheet. I picked Cells(999,999). Doing it this way can get you in trouble because the user can clear or overwrite the cell. Also, having a value in this cell will create problems for some operations such as finding the "last" cell. This cell will usually be the "last" cell. If any of these things are a problem for your code, you might want to keep the value in a small file that is created when the spreadsheet is loaded.
In the original version of this Quick Tip, I asked for other ideas. I got a few! I've added them at the end.
Changing the background color
The code here changes the background color of a cell can be by changing the color value of Selection.Interior.ThemeColor. This is new in Excel 2007. Microsoft added this feature to all Office 2007 programs so they could provide compatibility across them with the idea of "Themes". Microsoft has an excellent page explaining Office Themes at their site. Since I was unfamiliar with Office Themes, but I knew they would produce a nice shaded background, my initial try at changing the background color was to code:
Selection.Interior.ThemeColor = vbRed
Wrong! This doesn't work here. VBA kicks out a "subscript out of range" error. What subscript? Not all colors are represented in Themes. To get a specific color, you have to add it and vbRed didn't happen to be available. Using Themes in Office might work great in the user interface but it makes coding macros significantly more confusing. In Excel 2007, all documents have a Theme. If you don't assign one then a default is used.
This code will produce a solid red background:
Selection.Interior.Color = vbRed
To pick three shaded colors that actually work, I used the "Record Macro" feature and selected colors from the palette to get the "magic numbers" I needed. That gave me code like this:With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.599963377788629
.PatternTintAndShade = 0
I always say, "When in doubt, let the system do the work."
Avoiding an infinite loop
This is by far the most interesting problem to solve.
The code to do everything we have done so far (with some code deleted for simplicity) is:Private Sub Workbook_SheetChange( ...
If Cells(999, 999) < Cells(2, 2) Then
... cell shading code here
ElseIf Cells(999, 999) = Cells(2, 2)
... two more If blocks here
Cells(999, 999) = Cells(2, 2)
But when you run this code, the Excel task on your PC locks into an infinite loop. You have to terminate Excel to recover.
The problem is that shading the cell is a change to the spreadsheet which calls the macro which shades the cell which calls the macro ... and so forth. To solve this problem, VBA provides a statement that disables VBA's ability to respond to events.Application.EnableEvents = False
Add this to the top of the macro and reverse it by setting the same property to True at the bottom, and your code will run!
Other ideas for saving a value for comparison.
The first problem was saving the original value in the cell for comparison later. At the time I wrote this article, the only idea I had for doing that was to save it in some remote corner of the worksheet. I did mention that this might cause problems and asked if anyone else had a better idea. So far, I've received two of them.
Nicholas Dunnuck said that it might be easier and safer to simply add another worksheet and store the value there. He points out that cells in the same relative position could be used and that if the spreadsheet is backed up, these values will be backed up as part of it.
But Stephen Hall in the UK at LISI Aerospace came up with an even more direct way to do it. Many components in Visual Basic provide a Tag property for exactly this reason ... to save some random value associated with the component. Excel spreadsheet cells don't, but they do provide a comment. You can save a value there in direct association with the actual cell.
Great ideas! Thanks.