How do I add together only cells formatted in the same colour?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a budget spreadsheet with actual YTD, current orders & forecast sales
each are formatted in a different colour. I know there is a way to add
together only the cells formatted in a colour, but how is it done? Does the
formatting have to be a fill colour or - as I have - a text colour??

many thanks
 
Alt+F11 and paste this function into a workbook module

Function redcells(SelectedCells As Range)
Dim Cell As Object
Dim x As Double
x = 0
For Each Cell In SelectedCells
If Cell.Font.ColorIndex = 3 Then '< 3=red change as required
x = x + Cell.Value
End If
Next Cell
redcells = x
End Function

in a cell call the function with =redcells(A1:a100)

Mike
 
Thanks for coming back - I used to be able to write macros in early Excel,
but have never got to grips with visual basic. I have copied this as per
your note but, tp be honest, am a bit lost thereafter, so I shall have to go
and look at the Visual Basic stuff to work out if this answers my oroginal
problem.

Many thanks

Fizz Richardson
 
Thank you for the link - it fills in some of the Visual Basic gaps (great
holes rather) in my knowledge and now I can go and get this working rather
than selecting each cell individually to add them together as I have been
doing

Thanks

Fizz Richardson
 
Back
Top