Problem wqit huser-defined function

My objective is to sum all cells where the font color is blue. I am using this user-defined function:
Function SumColorF(Area As Range, ci As Integer)
Dim sng As Single
Dim rng As Range

For Each rng In Area
 If rng.Font.ColorIndex = ci Then
  sng = sng + rng.Value
 End If
 Next rng
 SumColorF = sng
End Function

Open in new window

When I enter into a cell = SumColorF(A2:A8,5) the value returned is zero, and I cannot see why.
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Can you upload a sample file for testing?
Hi fh,

When I tested it, it worked fine for me. Could your cells be a different blue than colorindex=5? You could put    ?selection.font.colorindex      in the immediate window to test the ci of a specific cell.

Otherwise, is there any way you could post a sample workbook?

Not that the result would be any different to your eyes, but you may want to change the Integer and Single variables to Long and Double, since VBA will convert them anyways. You could also change line 5 to look in each Area.Cells to force it. None of those updates would affect the outcome of the UDF though.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
You may have a point. I trusted that when I put my mouse over the color palet and it told me Blue I used that. Let me go back and double check.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Frank FreeseAuthor Commented:
The only color my system liked was red - so I know the code is right
Go figure
Frank FreeseAuthor Commented:
thanks Matt
I appreciate it
We could modify the function so that if you type in "Blue", it returns the count of any of the colors you choose to represent blue. Might be easier than forcing people to use the same blue as you.

See the colors available at:
Sub colors56()
'57 colors, 0 to 56
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual   'pre XL97 xlManual
Dim i As Long
Dim str0 As String, str As String
Range("A1:G1").Value = Array("Interior", "Font", "HTML", "RED", "GREEN", "BLUE", "COLOR")
For i = 0 To 56
  Cells(i + 2, 1).Interior.ColorIndex = i
  Cells(i + 2, 1).Value = "[Color " & i & "]"
  Cells(i + 2, 2).Font.ColorIndex = i
  Cells(i + 2, 2).Value = "[Color " & i & "]"
  str0 = Right("000000" & Hex(Cells(i + 2, 1).Interior.Color), 6)
  'Excel shows nibbles in reverse order so make it as RGB
  str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2)
  'generating 2 columns in the HTML table
  Cells(i + 2, 3) = "#" & str '& "#" & str & ""
  Cells(i + 2, 4).Formula = "=Hex2dec(""" & Right(str0, 2) & """)"
  Cells(i + 2, 5).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)"
  Cells(i + 2, 6).Formula = "=Hex2dec(""" & Left(str0, 2) & """)"
  Cells(i + 2, 7) = "[Color " & i & ")"
Next i
  Application.Calculation = xlCalculationAutomatic  'pre XL97 xlAutomatic
  Application.ScreenUpdating = True
End Sub

Open in new window

(modified from a function found at )
Frank FreeseAuthor Commented:
Thanks Matt - I'll keep this in my special module folder
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.