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 |
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 |
All times are GMT +1. The time now is 06:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com