automatic summation in columns

**standa** · 07-08-2008, 04:31 PM

I attached excel sheet with employee attendance. If you press CALCULATION BUTTON, then macro creates one sheet for each employee. In new created sheets is column with work time. So in the end of this column I would like to see sum of hours. Cell with total time should has blue colour.
Cells with employee name should have red colour and columns of lunch should have yellow colour.
Is somebody who knows how to adjust macro?

Thanks very much

**SOS** · 07-08-2008, 10:11 PM

Hi standa,

You could add the following code either as part of the CopyRecord2Tab Sub or as a separate Sub.

NB You need to format Column G with special format [h]:mm in order to total more than 24 hours.

Sub Add_Total_And_Colour()
Dim LastRow As Integer
LastRow = Range("F65536").End(xlUp).Row
    Range("G3").Value = "Total Hours"
    Range("G:G").NumberFormat = "[h]:mm"
        With Range("G4")
            .FormulaR1C1 = "=SUM(RC[-1]:R[" & LastRow & "2]C[-1])"
            .Interior.ColorIndex = 5
        End With
    Range("B1").Interior.ColorIndex = 3
    Columns("C:D").Interior.ColorIndex = 6
End Sub

**ravishankar** · 07-09-2008, 10:25 AM

Hi
I have modified the codes of seamus (SOS). attached is the file.
Ravi

**standa** · 07-09-2008, 01:51 PM

Thanks, but in first and last employee´s sheet is bad calculation of total work hours.

**ravishankar** · 07-09-2008, 11:36 PM

Hi
The first and last sheet has totals over 24 hours and other sheets do not.you would have got the problem in other sheets too when more rows were added.
to fix it add as prefix worksheets(a). to the code Range("G:G").NumberFormat = "[h]:mm" save it and run the macro.
Ravi