How to use XLL function in conditional formatting formula ?

[Charltsing Liu]

 I want write my xll udf in a conditional formatting formula dialog box, but Excel say " this type of reference cannot be used in a Conditional Formatting Formula"

How to use XLL function in conditional formatting formula ?

xll function:

public static double MyAdd(double d1, double d2)

        {

            return d1 + d2;

        }

I google this error ,and find some info:

" Conditional Formatting Formulas Must Have References - If you have a conditional formatting formula that references a UDF defined in another workbook such as an add-in, then you must add a Reference to that workbook before you can reliably use the functions defined in it.  If you don't, you will (sometimes) get the error 'This type of reference cannot be used in a Conditional Formatting Formula'. To add a reference, press Alt+F11 to go to the code editor, then go to Tools, References, and add the reference to this workbook by project name for the project containing the VBA code you need. "

But xll can not be references ,so anybody have some ideas?

[Charltsing Liu]

http://msdn.microsoft.com/en-us/library/bb687858.aspx

in this page: The condition and limit fields in a conditional format dialog box.(Calling XLL Functions Directly from Excel)

what is means conditional format dialog box?   I cannot call xll function in  conditional format formula

[Bill G]

I wrote a sub where I pass a range of the cells I want to apply the conditional formatting to.  Here is some sample code:


Hope this helps.

[Charltsing Liu]

where are some sample code?

在 2013年11月27日星期三UTC+8上午9时41分05秒，Bill G写道：

[Bill G]

 Not sure what happened... Here is my code snippit.

[Bill G]

I guess you can't post an image... here is the code:

'Formatting for a P&L cell
    Sub PnLFormat(PnLRange As Range)
        With PnLRange
            'Add Condition 1 - Green; (>0)
            .FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlGreater, 0)
            .FormatConditions(1).Interior.Color = RGB(0, 128, 0) 'Green
            .FormatConditions(1).font.colorindex = 2
            .FormatConditions(1).font.bold = True
            'Add Condition 2 - Red; (<0)
            .FormatConditions.Add(XlFormatConditionType.xlCellValue, XlFormatConditionOperator.xlLess, 0)
            .FormatConditions(2).Interior.Color = RGB(153, 0, 0) 'Dark Red
            .FormatConditions(2).font.colorindex = 2
            .FormatConditions(2).font.bold = True
        End With
    End Sub