code needs a bit of updating

[Akroyd]

This code works for me to total the coloured cells in the selection.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim total As Long
For Each cell In Selection
If cell.Interior.ColorIndex <> xlNone Then _
total = total + cell.Value
Next
MsgBox "The total of the coloured cells is " & total
End Sub


Does anybody know what I need to add to make it do a count as well, is it another (IF) or is there (undoubtably) a better way.

Thanks

[Mudface]

Just add a Dim x As Long to the top of your code, and split your If.. Then statement as follows:

If cell.Interior.ColorIndex <> xlNone Then
total = total + cell.Value
x=x+1
End If

Finally, you could change your message to: -

MsgBox "There were " & x " coloured cells, totalling " & total

[JayKay]

Hi Akroid

Updated code:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim total As Long, Count as long
For Each cell In Selection
If cell.Interior.ColorIndex <> xlNone Then _
total = total + cell.Value
count = count + 1
End if
Next
MsgBox "The total of the coloured cells is " & total & chr(13) & "The count of the coloured cells is " & count
End Sub

Hope this helped

Cheers

JayKay

[Mudface]

Jay- you need to remove your underscore after 'Then' otherwise your code will throw up an error.

[JayKay]

Yo Mudface

Good call!

JayKay

[Mudface]

Yo yourself .