• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

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 Freese
Frank Freese
  • 4
  • 2
1 Solution
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.

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.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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 http://dmcritchie.mvps.org/excel/colors.htm )
Frank FreeseAuthor Commented:
Thanks Matt - I'll keep this in my special module folder
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now