Murph said:
Can excel sum certain cells according to font color?
i.e. cash back expenses listed in red - summed to cell E4
credit card purchases listed in blue - sum to cell F4
the user manipulates the font color during input. But all values are
listed
in a Mon-Fri G1-K1 area
Not as a command but you can write a function to do it for you in VBA
like:
Code:
--------------------
Option Explicit
'constants to adapt the code easily if you want to use it for a difrent range or targetcells
Const FirstRow = 1
Const LastRow = 1
Const FirstColumn = 7 '(= "G")
Const LastColumn = 11 '(="K")
Const BlueFontTotalrow = 4
Const RedFontTotalrow = 4
Const BlueFontTotalColumn = 5
Const RedFontTotalColumn = 6
Sub AddOnFontColor()
'declar variable to count the rows and columns
Dim Rw As Long, Col As Long
'Variable to store the totals while counting
Dim RedSum As Double
Dim BlueSum As Double
'loop through the rows and columns
For Rw = FirstRow To LastRow
For Col = FirstColumn To LastColumn
'add the cells value to redsum if font is red
If Cells(Rw, Col).Font.Color = vbRed Then _
RedSum = RedSum + Cells(Rw, Col).Value
'add the cells value to bluesum if font is blue
If Cells(Rw, Col).Font.Color = vbBlue Then _
BlueSum = BlueSum + Cells(Rw, Col).Value
Next 'column
Next 'row
'place the totals in the desired cells
Cells(BlueFontTotalrow, BlueFontTotalColumn).Value = BlueSum
Cells(RedFontTotalrow, RedFontTotalColumn).Value = RedSum
End Sub
