Row number changes in conditional format formula during VB executi

[John K]

I have a spreadsheet with some cells that have conditional formatting. In
the Workbook_BeforePrint event, I have VB code that looks at cells that have
conditional formatting. It determines if the results of the conditional
formatting formula equate to True (and therefore turns on the formatting),
and then cancels the print event and displays a message. When I select the
print icon from the toolbar, all works fine.

Apparently, the print preview icon also runs the Workbook_BeforePrint event.
When I select the print preview icon, I have a problem. The cell references
within the conditional formatting formula are changed either only in VB or
during the execution of the code. After the code is run and the formulas in
the conditionally formatted cells are examined, you see no permanent changes
were made.

For example, column A of the spreadsheet is for dates and column B is for
amounts. The cells in A are conditionally formatted with a formula that
says, if the cell in column B of my row is not null and I am null (=B1<>â€â€
AND A1=â€â€), color me red. Assume A1 had a date entered and cells B1 and B2
have amounts. A2 is blank and therefore looks red.

Select the print icon the code runs properly like this:
Select a cell with conditional formatting A1
Look at the formula Format if = B1<>â€â€ AND A1=â€â€
Evaluate the formula True or False
Result is False
Select next cell with conditional formatting A2
Look at the formula Format if = B2<>â€â€ AND A2=â€â€
Evaluate the formula True or False
Result is True
Cancel print
Display message

Select the print preview icon the code runs like this:
Select a cell with conditional formatting A1
Look at the formula Format if = B1<>â€â€ AND A1=â€â€
Evaluate the formula True or False
Result is False
Select next cell with conditional formatting A2
Look at the formula Format if = B1<>â€â€ AND A1=â€â€
Evaluate the formula True or False
Result is False
No more cells to review
Print

Note in the second evaluation of the print preview scenario, the rows of the
conditional format formula are changed to the row of the cell previously
selected. When you have many rows of data to evaluate, the rows in the
conditional formatting formula always reference the row of the cell where the
prior evaluation took place. The formula in row 3 will change to look at
cells in row 2, the formula in row 4 will change to look at cells in row 3,
and so on. Even if there are several columns of cells with conditional
formatting and the code cycles left to right, top to bottom, the rows numbers
in the formula of the cell currently being evaluated are the row number of
the cell that was last selected.

Below is the actual code I have been using. Note that I left the message
boxes I was using to try to isolate the problem.

Sub Workbook_BeforePrint(Cancel As Boolean)

Dim IsRed As String
Dim objCell As Object
Dim CondFormula As String

IsRed = False 'Initialize value

ActiveSheet.Unprotect 'Unprotect in order to use SpecialCells

For Each objCell In
ActiveCell.SpecialCells(xlCellTypeAllFormatConditions).Cells 'For each
conditional formattted cell
objCell.Select
MsgBox "Before evaluate address " & ActiveCell.Address 'Show where am I
MsgBox "Before evaluate IsRed " & IsRed 'Show the value
of IsRed
CondFormula = Selection.FormatConditions(1).Formula1
MsgBox CondFormula 'Show
conditional formatting formula
IsRed = Evaluate(CondFormula) 'Is the
conditional format result True or False
MsgBox "After evaluate IsRed " & IsRed 'Show the value
of IsRed
If IsRed = "True" Then 'If conditional
formatting is turned on...
Cancel = "True" 'Stop printing
MsgBox "Key information is missing." & _
vbCrLf & vbCrLf & _
"See cells colored red.", _
vbExclamation, "Can not print..." 'Display error
message
GoTo Done 'Exit For Each
End If
Next objCell

Done:

'Protect sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
AllowSorting:=True

Application.ScreenUpdating = True

End Sub



Any ideas why VB is changing the row of the cell references?
If you respond, please consider that I am very new to VB. Therefore,
detailed explanations are much appreciated.

 

[Joel]

I don't see anything that would cause your problem in the BeforePrint Macro.
What I do see is the statement "Application.ScreenUpdating = True". If you
are turning screen updating on, the are you turning it off someplace else in
the code.

What I think is happening your are being fooled by the screen updating being
turned off. Running the Beforeprint Macro is actually turning on the
updating and then you are really seeing the results of changes made in your
other macros.

 

[John K]

Joel,

Thanks for the idea. I deleted this line, which was left over from prior
attempts to fix the error. Removing the Application.ScreenUpdating = True
his does not seem to correct the problem.

I had previously removed all other modules, macros, etc. to ensure none of
them was causing the problem.