Conditional Formatting & Custom Functions

[mikeyc1204]

(Using Excel 2002 SP2)

I'm having a problem using a custom function in a cell's conditional formatting, ONLY when I apply the formatting with VBA.

The function is very simple. It returns a cell's height. The goal is to have formatting apply only when the cell directly above is not hidden.

Function CellHeight(rng As Range)
    CellHeight = rng.Height
End Function

I want to apply the formatting to only certain cells that can only be determined at runtime.

Set rng = Cells(lngRows(i), j)
 
expression = "=IF(AND(CellHeight(" & rng.Offset(-1, 0).Address & ")>0,OR(AND(" & rng.Address & "<>"""",N(" & rng.Address & ")=0," & rng.Address & "=0),N(" & rng.Address & ")<>0)),TRUE,FALSE)"
 
rng.FormatConditions.Delete
rng.FormatConditions.Add Type:=xlExpression, Formula1:=expression
With rng.FormatConditions(1)
    .Borders.Weight = rng.Borders.Weight
    .Borders.LineStyle = rng.Borders.LineStyle
    .Interior.ColorIndex = rng.Interior.ColorIndex
    .Borders(xlTop).LineStyle = xlContinuous
    .Borders(xlTop).Weight = 2
    .Borders(xlBottom).LineStyle = xlContinuous
    .Borders(xlBottom).Weight = 2
    .Font.Bold = True
End With

The "expression" above translates to this:
=IF(AND(CellHeight($B$11)>0,OR(AND($B$12<>"",N($B$12)=0,$B$12=0),N($B$12)<>0)),TRUE,FALSE)

If I apply this formatting while working manually in the spreadsheet, it works fine. When I try to apply the formatting in VBA, execution halts. I don't get an error message or warning. The execution just stops at this line like it read an "END" statement.

rng.FormatConditions.Add Type:=xlExpression, Formula1:=expression

I thought at first that the line was written incorrectly and it was some trick of fate that I didn't get a debug message when running. But if I change the condition and leave out my CellHeight function, the code runs just fine. So unless there's something that flew over my head investigating this, whenever I apply a conditional format using a cunstom function in VBA, execution halts without warning.

Can anyone confirm this? Does anyone know a workaround, or a canned function to return the height of a row?

Thanks,