Setting a cell format based on a function operation

[scott56hannah]

Hi,

I am trying to setup a basic invoice sheet and I have started a function
that will
calculate the number of days owing based on the current date and the date of
the invoice.....it sets a value as per below for a cell in the invoice row

Function strDebtorDays(intDebtorDays As Integer) 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"
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

I now want to color the interior of the cell based on one of those values.
But because it is function I cannot work out how to update the cell format ?

Any help appreciated

Thanks
Scott

 

[JLGWhiz]

You would need a cell reference to add the color. I don't know how your
code is written to identify the cell where the value goes, i.e. as a
variable, or a specified cells or A1 reference. If you are using select or
activate to get to the cell, then
ActiveCell.Interior.ColorIndex = ? would work in the case statement.
Otherwise, use the actual range/cells reference.

 

[scott56hannah]

Hi,

I currently have this function setup on a cell in the worksheet to populate
the string (eg "7 days") based on other values in the row.....so when a
value changes the cell value is recalculated. At that point I want the format
changed based on the value.....so the cell is not really active when the
function is recalculated....

Is there anyway around this so that I can still format the cell depending on
that value. I also want to be able to format the cell range with more than 3
types of fill color so the conditional formatting will not work

Thanks
Scott

 

[JLGWhiz]

Then you should be able to use the specific cell reference in your case
statement to set the color like so:

'Assume Range{"$C$5") is the cell that strDebtorDays checks.
'Replace the ? with a number 1 - 56.
Select Case intDebtorDays

Case 0 To 7
strDebtorDays = "< 7 days"
Range("$C$5").Interior.ColorIndex = ?
Case 8 To 14
strDebtorDays = "< 14 days"
Range("$C$5").Interior.ColorIndex = ?
Case 15 To 30
strDebtorDays = "< 30 days"
Range("$C$5").Interior.ColorIndex = ?
Case 31 To 60
strDebtorDays = "< 60 days"
Range("$C$5").Interior.ColorIndex = ?
Case 61 To 90
strDebtorDays = "< 90 days"
Range("$C$5").Interior.ColorIndex = ?
Case Is > 90
strDebtorDays = "> 90 days"
Range("$C$5").Interior.ColorIndex = ?
Case Else
strDebtorDays = "Not Valid Range"

End Select

End Function

 

[scott56hannah]

Unfortunately I need to make it repeatable across each row that the detail is
in. But I think I can do that by passing in the cell reference to the
function call and then using that to perform the format setting...

Thanks for your help....