Calling a function within a macro
 

Hi,

I have a worksheet called SDB which consists of a row (1) of column headings
and then a row (2) of cell values/references/formulas etc. a second sheet
called Data in the same workbook holds data values from another source which
need to be organised/formatted in a diferent way (i.e. this is what the SDB
sheet does). My macro called SDB_copy selects row 2 of SDB and copies down to
the last lastrow of data (the lastrow is found using the following line of
code: lastrow = Sheets("Data").Cells(Cells.Rows.Count, "A").End(xlUp).row)

SDB has some cells which are conditionally formatted to have a red
background on certain conditions. I have a couple of functions that 1.
identify the colour index (CFColorindex) and 2. count the number of cells
which have the specified condition (CFColorCount).

I can call the functions from a cell in the worksheet using:
=CFColorCount(Z2:Z76,3)
which gives the right answer.

What I want to do is call the function from my SDB_copy macro and display
the result in a message box, for example

MsgBox("Please correct errors -" answer)

So far I have tried various ways of calling the function from within my
macro, such as:

Dim Res As Variant
Res = Application.Function.CFColorCount("Z2:Z76", 3)
MsgBox ("Please Rectify All Highlighted Records - " & Res)

but when I try to run the macro I get an error suggesting that the : in the
range is unexpected.

I would also like to be able to use the lastrow facility such that the range
to be counted is ("A2:A" & lastrow).

I know this is probably really simple but I just cant get the coding right,
all help is gratefully received!

I apologise if my explanation is a bit long, thanks in advance
Kind regards,
Amy