Excel Sum total based on font color, Automatic Black font color creates error while Sum and decimal value not included in sum

[Jyotirmaya]

Public Function ColorSum(ByVal target As Range, ByVal MyColor As String)
Dim Blacksum As Long, Othersum As Long, cel As Range
 Application.Volatile
 
 Blacksum = 0
 Othersum = 0
 For Each cel In target
 If IsNumeric(cel.Value) Then
 If cel.Font.ColorIndex = 1 Then
 Blacksum = Blacksum + cel.Value
 Else
 Othersum = Othersum + cel.Value
 End If
 End If
 Next cel

 ColorSum = IIf(LCase(MyColor) = "black", Blacksum, Othersum)

 End Function

1 . I am using the above code to calculate black colored sum totals and red
colored sum totals in different rows of the excel sheet, but as you know
there is a Automatic black color in Font options, when I am entering
values with that Automatic color ( Black ) it doesn't sum under Black
total, the Automatic Color (Black) cell value's total goes to Red color
total insted of Black color total sum, I want the Automatic Black sum
total should be included on Black sum total.



I am using A11=colorsum(A1:A10,"black")

A11=colorsum(A1:A10,"red")

2. when I am using the code if there is any decimal values such as 1.05+2.00+3.00 the total comes 6 instead of 6.05 when I am format cell numbers to 2 digit still it doesn't shows