Summing only selected font colour

[chrissy6244]

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]Q1 16/17[/TD]
[TD]Q2 16/17[/TD]
[TD]Q3 16/17[/TD]
[TD]Q4 16/17[/TD]
[TD] Q1 16/17[/TD]
[/TR]
[TR]
[TD]Staff[/TD]
[TD]500[/TD]
[TD]500[/TD]
[TD]400[/TD]
[TD]350[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Non Staff[/TD]
[TD][/TD]
[TD]300[/TD]
[TD][/TD]
[TD][/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]Student[/TD]
[TD]85[/TD]
[TD]20[/TD]
[TD]49[/TD]
[TD]16[/TD]
[TD]63[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]585[/TD]
[TD]520[/TD]
[TD]449[/TD]
[TD]366[/TD]
[TD]263[/TD]
[/TR]
</tbody>[/TABLE]

Black font : Agreed
Red font: Approved
Blue font: Potential

I would like to have only sum of Agreed value and Approved Value.
There are more than 1500 project(those 4 lines are 1 project), I don't think I can add up manually.
Please help me.

 

[Kenneth Hobson]

Welcome to the forum!

Is this for manually marked fonts, conditional format fonts, or both?

 

[chrissy6244]

Hi Kenneth
this is for both.

 

[Kenneth Hobson]

This can not be used a UDF.

Sub Test_SumByFontColor()
  MsgBox SumByFontColor(Range("B2:B4"), 3355443)  'Black
  MsgBox SumByFontColor(Range("B2:B4"), 255)      'Red
  MsgBox SumByFontColor(Range("B2:B4"), 16711680) 'Blue
End Sub

Function SumByFontColor(cRange As Range, aColor) As Double
  Dim c#, r As Range, ac&

  If TypeName(aColor) = "Range" Then
    ac = aColor.DisplayFormat.Interior.Color
    Else
      ac = aColor
  End If

  For Each r In cRange
    If r.DisplayFormat.Font.Color = ac And _
    IsNumeric(c) Then c = c + r.Value
  Next r
  
  SumByFontColor = c
End Function