How do I install a UDF?

[GeorgeA]

Hello,
I found this great post from Stefi but I don't know what it means to install
a UDF and can't find any reference in the Excel 2007 HELP files. Can someone
help me.

Install this UDF (post if you need assistance in installing), enter
=ColoredRow(ROW(),3) in all rows in an unused column! Autofilter the TRUE
values in this column, and copy them into a separate sheet!

Function ColoredRow(rownum, colorcode)
ColoredRow = False
For Each cella In Range(rownum & ":" & rownum)
If cella.Font.ColorIndex = colorcode Then
ColoredRow = True
Exit Function
End If
Next cella
End Function

Regards,
Stefi

 

[Dave Peterson]

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

=========
Remember that you have to allow macros to run for the UDF to work.

 

[Tom Hutchins]

Installing a User-Defined Function (UDF) means pasting the code into a
general VBA module. Then you can use the UDF like any other function in
regular Excel. This
link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Jon explains step-by-step how to get into the Visual Basic Editor, add a VBA
module, add the code, etc.

Hope this helps,

Hutch

 

[GeorgeA]

Thanks! It works now.

Installing a User-Defined Function (UDF) means pasting the code into a
general VBA module. Then you can use the UDF like any other function in
regular Excel. This
link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Jon explains step-by-step how to get into the Visual Basic Editor, add a VBA
module, add the code, etc.

Hope this helps,

Hutch

 

[GeorgeA]

Hi Tom,
I installed the UDF and it works, but only when the text is manually
formatted to Red. If I apply conditional formatting for Duplicates to format
the text into Red font, the UDF does not recognize it and returns FALSE
rather than TRUE.

Any chance you could help me out on this?

Thanks,
George

 

[Dave Peterson]

I would use another cell that mimics the formatting conditions and then check
that.

But if you really, really want (it ain't for the faint of heart!), take a look
at how Chip Pearson does it:
http://cpearson.com/excel/CFColors.htm

 

[Tom Hutchins]

There is no good way to detect the application of conditional formatting. The
easiest way by far is to use the same logic the conditional formatting is
using to figure out if the cell should be receiving formatting from the
conditional formatting. For example, if your conditional formatting colored
the text red if cells in column A have a value under 50, you can test that
and ColoredRows with an IF(OR formula like this:

=IF(OR(ColoredRow(ROW(A1),3),A1<50),TRUE,FALSE)

and copy down. I read that Excel 2010 may have new features that will allow
us to test formatting applied via conditional formatting.

Hope this helps,

Hutch