Count numbers with blue fonts

Posted in portal.gznq.com | edit | March 12th, 2010

Hi, in my speead sheet I have numbers from 1 to 20. All number fonts are black. but now I change 3 numbers into red font. for example, number, 2, 10, 8 are now in red font. Now out of 20 numbes, I want to count only those 3 numbers which are in red fonts. so it should give me red=3 .

any help.
Thanks in advance

Hello Amna, you need to have the code in the same workbook that you're working on. To do this, press alt-f11 to go to your Visual Basic Editor (VBE) (from the workbook), now click on insert->Module.

Now Paste the code above into the module. Close the VBE, go back to your worksheet and press shift-f9 to recalc.

Incidentally, changing the font colours after the formula's been entered will not inspire Excel to recalc., you need to either make a cell entry or do a manual recalc.

Thank you very much for great help.

Thanks a lot

Here is the file, can you please check what I am doing wrong here, Thanks

I try this same thing, I mean sam layout of numbers, but its not working.
Is there a way that I can send you my small file for kind review.
Thanks

Hello Amna, I recommend using a user defined function for this task.

Try placing the following function in a normal module in your workbook:



Function CountByColor(InputRange As Range, ColorRange As Range) As Long

Dim cl As Range, TmpCount As Long, ColorIndex As Integer

Application.Volatile

ColorIndex = ColorRange.Font.ColorIndex

TmpCount = 0

On Error Resume Next

For Each cl In InputRange.Cells

    If cl.Font.ColorIndex = ColorIndex _

        Then TmpCount = TmpCount + 1

Next cl

CountByColor = TmpCount

End Function

Now use the following following formula:

=CountByColor(A1:C5,D1)

Where a1:d6 is the range that holds the cells you're counting and F1 is a cell that has text with the targeted font colour. I'll follow up with a visual, my message is too long...

Okay, here's the visual of the function being used in the front end:

Here's a visual aid:

Microsoft Excel - Book3

___Running: xl2000 : OS = Windows Windows 2000

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout

My Colour

Sheet1

[HtmlMaker 2.20BETA] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

You can click on F2 to see the formula. Hope this helps.

#If you have any other info about this subject , Please add it free.#

Navigation

Calendar

Blog

Links

Feeds and Credits