Weird problem with conditional format

[Dallman Ross]

I have a long macro that creates a merge page and formats
it. It puts various conditional formatting in place.
One thing it does stopped working, and I don't know why.

It is the second conditional format below. Note how
similar it is to the first one. The first one works.
The second one used to work.

I did put a breakpoint in there and look, and
the conditional format was working at some point after
the submacro ran but before the full calling macro
completed. I can't figure out what's turning that off.

Also, and this seems important, if I select the range and
go into conditional formats manually and select the
formula thta isn't working, and simply press Enter,
then close the conditional formatting dialog, the thing
works as it should have all along.

Well, this code is long, but the only important parts
are the "Formula1" on the 4th line and the second instance
about halfway down. (The third one, near the bottom,
also works fine, as does the first one.)

Any ideas would be appreciated.

=dman=

Private Sub pmfOrderSubtotals()

'Conditional Formats for Column Z
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=MAX(IF(colZ<0,IF(LEN(colSymbol)<5,colZ)))"

With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With

Selection.FormatConditions(1).Interior.ColorIndex = 4

' WHY DOESN'T THIS WORK?!
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=MIN(IF(colZ>0,IF(LEN(colSymbol)<5,colZ)))"

With Selection.FormatConditions(2).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.FormatConditions(2).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.FormatConditions(2).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With Selection.FormatConditions(2).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
Selection.FormatConditions(2).Interior.ColorIndex = 8
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=VALUE($Q2)<2"
With Selection.FormatConditions(3).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 16
End With
With Selection.FormatConditions(3).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 16
End With
With Selection.FormatConditions(3).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 16
End With
With Selection.FormatConditions(3).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 16
End With
Selection.FormatConditions(3).Interior.ColorIndex = 19
End Sub

 

[Joel]

The real question is why does the first one work. I think both should fail.
Go to the cell on the worksheet why the 1st conditional format si inserted
and see if that is correct. I think the first one should be written as
follows

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=MAX(IF(" & colZ & "<0,IF(LEN(" & _
colSymbol & ")<5," & colZ & ")))"

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=MIN(IF(" & colZ & ">0,IF(LEN(" & _
colSymbol & ")<5," & colZ & ")))"

 

[Dallman Ross]

The real question is why does the first one work. I think both should fail.
Go to the cell on the worksheet why the 1st conditional format si inserted
and see if that is correct. I think the first one should be written as
follows

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=MAX(IF(" & colZ & "<0,IF(LEN(" & _
colSymbol & ")<5," & colZ & ")))"

Joel,

Thanks for helping.

I have verified it many times. It is as I posted. The sheet
gets recreated every single time I run the macro. (It's a merge
sheet combining data from other sheets.) Every time, the first one
"lights up" (formats the fill color as) green in the right cell.
The other one doesn't work until I highlight the range, go into
conditional formats, go to either formula, and hit the Enter key.
Then it works.

The formula also works when I simply plug it into a cell in order
to give a result, instead of using it in a conditional format.

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=MIN(IF(" & colZ & ">0,IF(LEN(" & _
colSymbol & ")<5," & colZ & ")))"

Let me try yours, though. Hold on:

Nope. I see now that yours is like mine, except you presumed those
names were VBA variables. They are not. They are named ranges
in the worksheet. I apologize for not making that clear before.

The issue remains, and remains baffling.

=dman=

=====================================

 

[Joel]

There are too many combination of possible problems for me to give an exact
answer. Put both formulas in two different worksheet cells and see the
results. Use on the Tools Menu - Formula Auditing -Evalute formual. Do this
for both cells and find out where the problem is located.

 

[Dallman Ross]

There are too many combination of possible problems for me
to give an exact answer. Put both formulas in two different
worksheet cells and see the results. Use on the Tools Menu -
Formula Auditing -Evalute formual. Do this for both cells and
find out where the problem is located.

No problem shows up. They are array formulas, but I have found that
array formulas work in conditional formats. As soon as I go into
CF to copy the formula and go back out, the condition works again
and the cell highlights as I wanted. I nevertheless paste the
copied formula from the CF dialog into a cell, hit Ctrl-Shift-Enter
to enable the array formula, and run the Evaluate feature from
Formula Auditing. It runs through the formula step-by-step and
shows no error.

At this point, I think this is a bug in my Excel 2002 SP3.

=dman=

=================================================