Formula Help

[LynnyWeb]

Hi,

I am creating a spreadsheet to record staff holidays, I am using the colorfunction formula however I need it to add up 12 rows. At the moment it looks like this =colorfunction(I2,H7:AL7) but I also need it to add H27:AL27, H47:AL47, etc how do I add this into the formula?

I was thinking =colorfunction(I2,H7:AL7+H27:AL27) but dont think thats right?


Also I need a way for the colour function to count half days or half a cell if this is possible? Any ideas would be greatly appreciated?

Thanks
<!-- / message -->

 

[Richard Schollar]

Hi

colorfunction is not a native Excel formula which means it must be a user-defined function? We won't be able to assist unless we can see the code behind this UDF and a brief summary of what it is meant to do.

Also, it would greatly help if you could post a sample of your data so that we can see what will be in the cells. You should be able to use a html maker such as the one linked in my signature.

 

[LynnyWeb]

Ok thanks, apologies if I'm a bit slow at this but I have two modules, see below:

1st Module

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums or counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''

lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

2nd Module

Sub Save_Hols()
'
' Save_Hols Macro
'
'
Range("D7").FormulaR1C1 = "=colorfunction(R[-5]C[5],RC[4]:RC[34])"
Range("D8").FormulaR1C1 = "=colorfunction(R[-6]C[5],RC[4]:RC[34])"
Range("D9").FormulaR1C1 = "=colorfunction(R[-7]C[5],RC[4]:RC[34])"
Range("D10").FormulaR1C1 = "=colorfunction(R[-8]C[5],RC[4]:RC[34])"
Range("D11").FormulaR1C1 = "=colorfunction(R[-9]C[5],RC[4]:RC[34])"
Range("D12").FormulaR1C1 = "=colorfunction(R[-10]C[5],RC[4]:RC[34])"
Range("D13").FormulaR1C1 = "=colorfunction(R[-11]C[5],RC[4]:RC[34])"
Range("D14").FormulaR1C1 = "=colorfunction(R[-12]C[5],RC[4]:RC[34])"
Range("D15").FormulaR1C1 = "=colorfunction(R[-13]C[5],RC[4]:RC[34])"
Range("D16").FormulaR1C1 = "=colorfunction(R[-14]C[5],RC[4]:RC[34])"
Range("D17").FormulaR1C1 = "=colorfunction(R[-15]C[5],RC[4]:RC[34])"
Range("D18").FormulaR1C1 = "=colorfunction(R[-16]C[5],RC[4]:RC[34])"
Range("D19").FormulaR1C1 = "=colorfunction(R[-17]C[5],RC[4]:RC[34])"
Range("D20").FormulaR1C1 = "=colorfunction(R[-18]C[5],RC[4]:RC[34])"
Range("D21").FormulaR1C1 = "=colorfunction(R[-19]C[5],RC[4]:RC[34])"
Range("D22").FormulaR1C1 = "=colorfunction(R[-20]C[5],RC[4]:RC[34])"
Range("F7").FormulaR1C1 = "=colorfunction(R[-5]C[9],RC[2]:RC[32])"
Range("F8").FormulaR1C1 = "=colorfunction(R[-6]C[9],RC[2]:RC[32])"
Range("F9").FormulaR1C1 = "=colorfunction(R[-7]C[9],RC[2]:RC[32])"
Range("F10").FormulaR1C1 = "=colorfunction(R[-8]C[9],RC[2]:RC[32])"
Range("F11").FormulaR1C1 = "=colorfunction(R[-5]C[9],RC[2]:RC[32])"
Range("F12").FormulaR1C1 = "=colorfunction(R[-10]C[9],RC[2]:RC[32])"
Range("F13").FormulaR1C1 = "=colorfunction(R[-11]C[9],RC[2]:RC[32])"
Range("F14").FormulaR1C1 = "=colorfunction(R[-12]C[9],RC[2]:RC[32])"
Range("F15").FormulaR1C1 = "=colorfunction(R[-13]C[9],RC[2]:RC[32])"
Range("F16").FormulaR1C1 = "=colorfunction(R[-14]C[9],RC[2]:RC[32])"
Range("F17").FormulaR1C1 = "=colorfunction(R[-15]C[9],RC[2]:RC[32])"
Range("F18").FormulaR1C1 = "=colorfunction(R[-16]C[9],RC[2]:RC[32])"
Range("F19").FormulaR1C1 = "=colorfunction(R[-17]C[9],RC[2]:RC[32])"
Range("F20").FormulaR1C1 = "=colorfunction(R[-18]C[9],RC[2]:RC[32])"
Range("F21").FormulaR1C1 = "=colorfunction(R[-19]C[9],RC[2]:RC[32])"
Range("F22").FormulaR1C1 = "=colorfunction(R[-20]C[9],RC[2]:RC[32])"
Range("G7").FormulaR1C1 = "=colorfunction(R[-5]C[14],RC[1]:RC[31])"
Range("G8").FormulaR1C1 = "=colorfunction(R[-6]C[14],RC[1]:RC[31])"
Range("G9").FormulaR1C1 = "=colorfunction(R[-7]C[14],RC[1]:RC[31])"
Range("G10").FormulaR1C1 = "=colorfunction(R[-8]C[14],RC[1]:RC[31])"
Range("G11").FormulaR1C1 = "=colorfunction(R[-9]C[14],RC[1]:RC[31])"
Range("G12").FormulaR1C1 = "=colorfunction(R[-10]C[14],RC[1]:RC[31])"
Range("G13").FormulaR1C1 = "=colorfunction(R[-11]C[14],RC[1]:RC[31])"
Range("G14").FormulaR1C1 = "=colorfunction(R[-12]C[14],RC[1]:RC[31])"
Range("G15").FormulaR1C1 = "=colorfunction(R[-13]C[14],RC[1]:RC[31])"
Range("G16").FormulaR1C1 = "=colorfunction(R[-14]C[14],RC[1]:RC[31])"
Range("G17").FormulaR1C1 = "=colorfunction(R[-15]C[14],RC[1]:RC[31])"
Range("G18").FormulaR1C1 = "=colorfunction(R[-16]C[14],RC[1]:RC[31])"
Range("G19").FormulaR1C1 = "=colorfunction(R[-17]C[14],RC[1]:RC[31])"
Range("G20").FormulaR1C1 = "=colorfunction(R[-18]C[14],RC[1]:RC[31])"
Range("G21").FormulaR1C1 = "=colorfunction(R[-19]C[14],RC[1]:RC[31])"
Range("G22").FormulaR1C1 = "=colorfunction(R[-20]C[14],RC[1]:RC[31])"
End Sub


Then in my cell I have the =colorfunction(I2,H7:AL7)

H7:AL7 being one row that it counts the color in cell I2, however I also want it to count the colour in other rows, namely H27:AL27 etc.

I hope I'm making sense, my laptop will not allow me to use the HTML Maker otherwise I would have done that.

Thanks.

 

[Richard Schollar]

Try this variant of the colorfunction (you need to copy in the code and then change your formulas to use CountColorCell). Note this function only counts - it won't sum.

Function CountColorCell(rColorRef As Range, ParamArray Args() As Variant)
'this function counts cells formatted with the reference colour
'provided by rColorRef cell
Dim vItm As Variant
Dim lngCnt As Long, lngColIndx As Long
Dim cell As Range
Application.Volatile
lngColIndx = rColorRef(1).Interior.ColorIndex
For Each vItm In Args
    If TypeName(vItm) = "Range" Then
        For Each cell In vItm.Cells
            If cell.Interior.ColorIndex = lngColIndx Then lngCnt = lngCnt + 1
        Next cell
    End If
Next vItm
CountColorCell = lngCnt
End Function

Use in a cell like:

=CountColorCell(I2,H7:AL7,H27:AL27,H45:AL45,H96)

 

[LynnyWeb]

Thats great thankyou very much, your not aware of anyway I could count half a cell are you? I know its probably a ridiculous question but aswell as full days holiday people need to record half days?

 

[Richard Schollar]

It would be much better to perform your count based on cell contents rather than cell formatting (ie so if cell contained D count 1, if contained H count half rather than if cell completely red count 1, if cell half red count 0.5 and so on). This is because 1) you won't need VBA and so it is much easier to maintain and 2) it's just more obvious to users.

I am afraid I am using a version of Excel which doesn't permit half-coloured cells (well, it doesn't seem to anyway) and so I am not sure how this is being achieved in your spreadsheet (are you using xl2007 or higher?). It is quite possible the code could be amended to do what you want, but like I said, it would be much easier if you based your count on the cell contents.