Custom function for conditional formatting

[RM2106]

Hi,
I am a novice to vba.
I am trying to create a custom function to compare a given date with today's date and

if the difference between the given date and today's date is more than 14 days then
display the difference in dates in the worksheet cell and highlight it.
else,
display the difference in dates in the worksheet cell

my code looks as follows:

Function TestDates(pDate1 As Date) As Long
 
   TestDates = DateDiff("d", pDate1, Date)

End Function


Function cond_format(dt As Date) As Variant

If TestDates(dt) > 14 Then

    cond_format = TestDates(dt)
    Debug.Print cond_format
    ActiveCell.Cells.Interior.Color = RGB(0, 0, 255)
    
ElseIf TestDates(dt) <= 14 Then

    cond_format = TestDates(dt)
    Debug.Print cond_format

End If

End Function

My code is working properly, if TestDates(dt) <= 14, else it is displaying #VALUE! in the worksheet.

Please help!!

 

[mart37]

Found this on internet.

Limitations of UDF's
Cannot "record" an Excel UDF like you can an Excel macro.
More limited than regular VBA macros. UDF's cannot alter the structure or format of a worksheet or cell.
If you call another function or macro from a UDF, the other macro is under the same limitations as the UDF.
Cannot place a value in a cell other than the cell (or range) containing the formula. In other words, UDF's are meant to be used as "formulas", not necessarily "macros".
Excel user defined functions in VBA are usually much slower than functions compiled in C++ or FORTRAN.
Often difficult to track errors.
If you create an add-in containing your UDF's, you may forget that you have used a custom function, making the file less sharable.
Adding user defined functions to your workbook will trigger the "macro" flag (a security issue: Tools > Macros > Security...).