User Defined function to format

[Guest]

Hi,

I got a user defined function as listed below:

Public Function EnteredWeek(rWeekEndDate As Range) As String
Dim bTimeSheetDayNow As Byte
Dim dEnteredWeekEndDate As Date

dEnteredWeekEndDate = CDate(rWeekEndDate.Value) <--Breakpoint
bTimeSheetDayNow = CByte(WorksheetFunction.Weekday(dEnteredWeekEndDate))

If bTimeSheetDayNow <> 3 Then
EnteredWeek = "CAUTION - The date entered for Wednesday is incorrect"
Sheet1.Range("F3:H3").Interior.ColorIndex = 3
Exit Function
End If

If CDate(rWeekEndDate.Value - 1) = 3 And CDate(rWeekEndDate.Value) =
Date Then EnteredWeek = ""
rWeekEndDate.Interior.Color = 12632256
End Function


My problem is if
bTimeSheetDayNow <> 3
is TRUE, the code won't execute
Sheet1.Range("F3:H3").Interior.ColorIndex = 3
and I will get "#VALUE" on my worksheet. Could someone explain to me why
Excel won't execute, but I can excute it inside Immediate Window(at
Breakpoint)?
The rWeekEndDate is in 31/07/2007 format.

Thanks.
Augustus

 

[NickHK]

A UDF can only return a value to its cell (and a few other minor actions).
So it cannot format a cell.

You could some conditional formatting though.

NickHK