Hi Carlos

I'm thinking of upgrading to office 2007 from 2003, are you aware of any differences with the code/layout in the new office package?

Also, do you remember our conversation about counting coloured cells?

Thanks
Nathan

Nathan

I have run the code I usually use in the course in 2007 and there have been no problems.

The Visual Basic Editor (VBE) hasn't had any major changes

As for the code to count coloued cells do the following:

1. Open the VBE and create a Module in either the document where the code is to run or in the Personal Macro Workbook.

2. Copy the following code into the module:

Public Function CountByColour(MyRange As Range, MyColorIndex As Integer, _
Optional OfText As Boolean = False) As Long

' This function return the number of cells in MyRange with 
' a background color, or if OfText is True a font color, 
' equal to MyColorIndex.
'
Dim RangeVar As Range

Application.Volatile True

For Each RangeVar In MyRange.Cells

If OfText = True Then

CountByColour = CountByColour - (RangeVar.Font.ColorIndex = MyColorIndex)

Else

CountByColour = CountByColour - (RangeVar.Interior.ColorIndex = MyColorIndex)

End If

Next RangeVar

End Function

3. Now in the spreadsheet select the cell where the count value is to go

4. In the function dialog box select User Defined and then select the CountByColour function as Below:

In the example 5 is the Colour Index for Blue

To count cells where the Background is Blue

=CountByColour(A1:A13,5)

To Count cells where the Text is Blue

=CountByColour(A1:A13,5, TRUE)

Hope this helps

Carlos