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

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

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

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

"Fizz Richardson" wrote:

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

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

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

"Fizz Richardson" wrote:

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

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

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

"Mike" wrote:

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

"Fizz Richardson" wrote:

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

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

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
"JMay" wrote:

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

"Fizz Richardson" wrote:

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