Getting a function to return a formated Range string

[Bob Hillier]

How do I get a function to return a formatted string for display in the
cell?
A1 has "=RollupStatus(A2:A5)"
A2 is GREEN background
A3 is YELLOW background
A4 is RED background
A5 is GREEN background
I want A1 to contain #### where 2 of the # characters are GREEN, 1 is Yellow
and 1 is RED

The function I wrote appears below. The problem I have is getting it to
return the formatted string. I've not initialized the range called
RollupStatus properly and I don't know how to correct it. My function code
is:

Function RollupStatus(InRange As Range) As Range
' This function counts the number of colour cells in InRange and
' sets a status string to match the range status.
' This doesn't deal with blank colour yet... xlColorIndexNone

Dim Rng As Range

Application.Volatile True
colourindex = 0
StartIndex = 0

Do While colourindex <= 56
CountByColor = 0
For Each Rng In InRange.Cells
' count each cell in range that matches current colourindex
CountByColor = CountByColor - (Rng.Interior.ColorIndex = colourindex)
Next Rng
If CountByColor > 0 Then
RollupStatus.FormulaR1C1 = RollupStatus.FormulaR1C1 &
String$(CountByColor, "#")
RollupStatus.Characters(Start:=StartIndex, Length:=CountByColor).Font
StartIndex = StartIndex + CountByColor
End If
colourindex = colourindex + 1 ' switch to next colour
Loop
End Function


Can anyone help me?

Thanks
Bob

 

[Frank Kabel]

Hi bob
a function can only return values. So you can't return a formated
string as result of a function. You may use either conditional format
or use the worksheet_change event

Frank