CODEWITHSUNDEEP
excelexcel-2007user-defined-functionsconditional-formatting
Ien
Ien

Reputation: 11

Use UDF from Add-in in Conditional Formatting

I have an add-in xla file that I use to store my regularly used VBA code. This function is stored in the add-in modules.

Public Function IsFormula(cell_ref As Range)
    IsFormula = cell_ref.HasFormula
End Function

This correctly returns True or False if I type it into cell: =IsFormula(A1)

However, when I try to create a new formatting rule using the formula option, I get this error 'You cannot use references to other worksheets or workbooks for Conditional Formating criteria.' The error is not because of quotation marks.

Upvotes: 1

Views: 491

Answers (1)

pnuts
pnuts

Reputation: 59485

There isn't exactly a clear question to be answered here, but if you want to format say all of the cells in Sheet1 that contain formulae then, in Sheet1 A1:

1) define a name (say ‘Formulaic’, with ‘Sheet1’ for “Scope” and =GET.CELL(48,A1) for “Refers to”.

2) Select Sheet1

3) Set required conditional format with “Use a formula to determine which cells to format” and =Formulaic in “Format values where this formula is true:”

The ‘type_num’ (eg 48 above) is described at http://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html

Upvotes: 1

Related Questions