Returning text from a VBA function

[ROHPitMan]

Does anyone know of a way to colour code the text string returned from a VBA function? I have written a function that returns a string of the days of the week worked e.g. M*W*FS* (where *=day off) and I want to turn Sat or Sun red if they are present (i.e. not *), but the range.characters method doesn't work. Is it possible to create a string with embedded colour? Does anyone know how to colour parts of a string such as this? I'd appreciate any thoughts.

Cheers

 

[Haluk]

Hi;

is it possible for you to post the code ?

Edit:

Or, may be something like this may help you;

[A1].Characters(Start:=1, Length:=5).Font.ColorIndex = 3
This message was edited by Raider on 2002-11-02 15:28

 

[ROHPitMan]

Thanks for the quick response. Here is the function:
Function DayList(ID As Integer, LookUp As Range)
Dim DayOfWeek(7) As Boolean
Dim lData As Integer, lFree As Integer, DayBinary As Integer
Dim DayOfWeekInc As Long
Dim QueryCell As Object
Dim DayLetters As String, DayListString As String, QueryWorksheet As String
DayListString = "": DayLetters = "MTWTFSS"
QueryWorksheet = "Weekly Breakdown (" + Format(ID) + ")"
With Worksheets(QueryWorksheet)
For Each QueryCell In LookUp
If .Cells(QueryCell.Row, 2) = 1 Then
lData = QueryCell.Value
If lData <> 0 Then
DayBinary = 1
For DayOfWeekInc = 1 To 7
If lData And DayBinary Then DayOfWeek(DayOfWeekInc) = True
DayBinary = DayBinary * 2
Next
End If
End If
Next
End With
For DayOfWeekInc = 1 To 7
If DayOfWeek(DayOfWeekInc) = True Then
DayListString = DayListString + Mid(DayLetters, DayOfWeekInc, 1)
Else
DayListString = DayListString + "*"
End If
Next
DayList = DayListString
End Function

This is called by:
=IF($J10<>"",DayList(1,'Weekly Breakdown (1)'!$BE$4:$BE$73),"")

The background to this spreadsheet is a long story, but basically enables an orchestra member to view the impact of a combination of projects on their working week. The function listed above displays which days of the week they will be working on, and changes dynamically when the user changes the projects they may wish to work on.
The line of sheet code is repeated once for each year week, calling a different range on the 'Weekly Breakdown (x)' sheet each time.

 

[Swamp Thing]

Have you considered conditional formatting?

(There is no mechanism by which a function can return a formatted string when called from a cell.)
This message was edited by Swamp Thing on 2002-11-02 20:42

 

[ROHPitMan]

The problem is that I only want to colour either if the two S's if a Saturday or Sunday is worked, not the rest of the letters. I've tried a separate Sub to change the colour, but although I can debug.print the contents of the cell (e.g. M**T*S*), .Characters cannot seem to format the displayed string. I was hoping to avoid this (because its messy), but the only way forward I can see is to have a separate cell for each day and conditional format them if they are different from "*" 8-(