Microsoft Excell VBA Code

[drewduncan]

Can anyone help:

I am trying to create a Holiday Schedule in Excel.

Every month is on a different page.

Each member of staff is colour coded.

Cells will be colour coded to show who is on holiday when.

I would like to have a field on each page to show the total number of holidays taken for each month for each member of staff.

The only way i can think of doing this is by 'adding the like coloured cells together'.

Can this be done in VBA Code?

Or does anyone else have a better idea.

Thanks in advance

 

[pc66]

try this link for some helpful answers to your question on Colours

http://www.cpearson.com/excel/colors.htm

Best solutions would be using this function
being an extract from Chip Pearsons site
**************************************
Counting Cells With A Specific Color

The following function will return the number of cells in a range that have either an Interior (background) or Font of a specified color. InRange is the range of cells to examine, WhatColorIndex is the ColorIndex value to count, and OfText indicates whether to return the ColorIndex of the Font (if True) or the Interior (if False).

Function CountByColor(InRange As Range, WhatColorIndex As
Integer, Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

You can call this function from a worksheet cell with a formula like
=COUNTBYCOLOR(A1:A10,3,FALSE)