The conditional format may be set incorrectly when you use VBA in Excel 2000 or in a later version (895562)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000

SYMPTOMS

Consider the following scenario.
  • You select a cell in a Microsoft Excel worksheet in Microsoft Excel 2000 or in a later version.
  • You use Microsoft Visual Basic Applications (VBA) to create a formula-based conditional format program.
  • You use relative cell references in the VBA conditional format program.
  • You apply the conditional format to a cell other than the selected cell.
When you apply the conditional format, you notice that the conditional format is not set correctly.

For example, you experience this problem when you use a program that includes VBA code in an Excel worksheet that is similar to the following:

Sub Example()
ThisWorkbook.Worksheets(1).Range("A1").Select
With ThisWorkbook.Worksheets(1).Range("B1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=A1=1"
.FormatConditions(1).Interior.ColorIndex = 46
End With
End Sub

This code automatically applies the conditional format to cell B1 when you enter "1" in cell A1. When you enter "1" in cell A1, you expect the color of cell B1 to change to red. However, the color of the cell does not change. The color of cell B1 changes to red only if you enter "1" in cell B1. Additionally, the Conditional Formatting dialog box on the Format menu shows the formula as =B1=1 instead of =A1=1.

WORKAROUND

To work around this problem, use one of the following methods.

Method1: Use absolute cell references

You can use absolute cell references to refer to the cell that contains the formula instead of to refer to relative cell references. For example, you can modify the Formula1:="=A1=1" text entry in the VBA code that was included in the "Symptoms" section as Formula1:="=$A$1=1" to make the code use absolute cell references. This modified version of the VBA code is as follows: 

Sub Example()
ThisWorkbook.Worksheets(1).Range("A1").Select
With ThisWorkbook.Worksheets(1).Range("B1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$A$1=1"
.FormatConditions(1).Interior.ColorIndex = 46
End With
End Sub

Method 2: Select the cell that you want to use for conditional formatting before you apply the formula

When you want to apply a conditional format to a cell, first select the cell that you want to use for the conditional format. Then, select the cell that you want to use for the formula. After you select this cell, modify the formula to suit your requirements. To do this, follow these steps:
  1. Start Excel, and then open a new Excel worksheet.
  2. In the Excel worksheet, click to select cell B1.
  3. On the Format menu, click Conditional Formatting.
  4. Under Condition 1, click Formula Is from the list.
  5. Click inside the data entry box. Then, click to select the cell that you want to use for the conditional format.
  6. Modify the value in the data entry box to read as =$A$1=1, and then click OK.
  7. Click Format.
  8. In the Format Cells dialog box, click the Patterns tab.
  9. Select the color "red," and then click OK.
  10. In the Conditional Formatting dialog box, click OK.
  11. In cell A1, type 1, and then press ENTER.
  12. Verify that the color of the cell B1 changed to red.
  13. Close the Excel worksheet.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

For more information about conditional formatting, click Microsoft Excel Help on the Help menu, type change, add, or remove conditional formats in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:3/23/2005
Keywords:kbtshoot KbVBA kbformat KB895562 kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.