Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a function to return a formated Range string
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting a function to return a formated Range string
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 Bob Hillier wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL function;find string in entire column & return cell referenc | Excel Worksheet Functions | |||
Return a String in a 7 Row Range Q | Excel Worksheet Functions | |||
function like INDIRECT to return a range? | Excel Worksheet Functions | |||
return cell address of longest text string in a range | Excel Discussion (Misc queries) | |||
Return string array from function in excel 97 | Excel Programming |