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" wrote:

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(colZ0,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