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

In , Joel
spake thusly:

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=

=====================================
"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