Q:

VBA Excel Function to Set the Calling Cell .NumberFormat Property

I created this function and it works to put the value into the cell but it doesnt work to set the .NumberFormat property. 
Public Function NewYears(year As Integer)
    'Determine the cell that called the function
    Dim rng As range
    Set rng = ThisWorkbook.Sheets("How-To").range(Application.Caller.Address)
    MsgBox rng.Address
    fxFormat = "[$" & holidayName & "]"
    NewYears = DateSerial(year, 1, 1)
    rng.NumberFormat = fxFormat  
End Function

Update For more information:
I will be having functions like =NewYears() that returns a date.  
I will do this for any number of holidays.  I would like to format the field where it still stores the date but the .NumberFormat property has the name of the holiday
So =NewYears() would return "01/01/2014" but in the sheet it would appear as "New Years"

A:

Use the Worksheet_Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Interior.ColorIndex = 39
    Target.NumberFormat = "mm/dd/yyyy" 
End Sub

Put this code in the Sheets("How-To") code module.  Modify to whatever color/etc that you want to format.
When you initially enter the function in the cell, it will trigger the change event  and this subroutine will execute.
Per Gary's comments (below), recalculation of existing formula will not trigger this event.

