Formatting not updating inside a function

[scott56hannah]

Hi,

I have developed a basic function that will include a string value to a cell
and then format that cell's background. The string value goes in ok but the
format of the cell's background does not work. If I extract the format code
into it's own macro it updates the cell contents without a problem....can
anyone suggest where I am going wrong ?

Cell has the following values to call the function
=strDebtorDays(H5,ADDRESS(ROW(),COLUMN()))
Where H5 is 7

Function strDebtorDays(intDebtorDays As Integer, strCellReference As String)
As String
'This function will return a string value showing the number of days that
the debt has been owed

Select Case intDebtorDays

Case 0 To 7
strDebtorDays = "< 7 days"

'Update the formating of the cell we are referencing
Range(strCellReference).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

Case 8 To 14
strDebtorDays = "< 14 days"
Case 15 To 30
strDebtorDays = "< 30 days"
Case 31 To 60
strDebtorDays = "< 60 days"
Case 61 To 90
strDebtorDays = "< 90 days"
Case Is > 90
strDebtorDays = "> 90 days"
Case Else
strDebtorDays = "Not Valid Range"

End Select

End Function

 

[JE McGimpsey]

UDFs called from the worksheet can't change cell format (just as with
built-in functions).

To change format, you'd need to use an event macro instead.

 

[scott56hannah]

Was the article below supposed to be a link ? if it was it did not come through

Scott

 

[JE McGimpsey]

Yes, it's a link, but it's probably irrelevant to you - it's the
reference to your original article.