Countif cell coloured - again

[Francis Hookam]

Tom Stiller rightly pointed out
"You're counting color occurrences of color '3' when you seem to be
interested in occurrences of color '36'."

but still I cannot make sense of it which is why I wrote the following - I
should be grateful if someone can help

Thanks (JE McGimpsey) for - http://cpearson.com/excel/colors.htm set out
below,
but I don¹t know enough to understand how to implement it
- I have tried pasting the function into

Workbook1 - This Workbook(Code)

adding

DIM WhatColorIndex as integer

and

WhatColorIndex = 36 ŒLight Yellow

and pasting

=COUNTBYCOLOR(A1:A10,3,FALSE)

into A11

with some of the cells in A1:A10 coloured Light Yellow

but I have obviously got something wrong - clearly I do not understand the
use of functions (after all these years!)

As always your help will is much appreciated

Francis Hookham
==========================================================
Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

 

[Bernard Rey]

- I have tried pasting the function into

Workbook1 - This Workbook(Code)

You should paste it in a new Module, not in the "ThisWorkbook (Code)" sheet:
In the VBE, from the "Insert" menu, pick the "Module" item. In the
"Workbook1 - Module1 (Code)" sheet, paste the macro.

adding

DIM WhatColorIndex as integer

and

WhatColorIndex = 36 ŒLight Yellow

There's no point (and no need) to do this. Don't add anything, just close
the VBE and go back to your Worksheet.

and pasting

=COUNTBYCOLOR(A1:A10,3,FALSE)

into A11

If it's color 36 (light yellow) you want to test, you should type:

=COUNTBYCOLOR(A1:A10,36,FALSE)
(or =COUNTBYCOLOR(A1:A10,36) which is equivalent)

That way you'll know how many cells in range A1:A10 are colored in
Lightyellow.

Typing =COUNTBYCOLOR(A1:A10,3,FALSE) as you did, would tell you how many
plain red cells you have.


---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - office:macintosh http://faq.office.macintosh.free.fr


Francis Hookam wrote :

 

[Francis Hookam]

Thanks Bernard - so we can only count the coloured cells but not sum the
values in the cells of a particular colour, which is what I really need -
pity - I shall have to write a macro which can be whistled up easily - maybe
running from "auto_open"

Francis

- I have tried pasting the function into

Workbook1 - This Workbook(Code)

You should paste it in a new Module, not in the "ThisWorkbook (Code)" sheet:
In the VBE, from the "Insert" menu, pick the "Module" item. In the
"Workbook1 - Module1 (Code)" sheet, paste the macro.

adding

DIM WhatColorIndex as integer

and

WhatColorIndex = 36 ŒLight Yellow

There's no point (and no need) to do this. Don't add anything, just close
the VBE and go back to your Worksheet.

and pasting

=COUNTBYCOLOR(A1:A10,3,FALSE)

into A11

If it's color 36 (light yellow) you want to test, you should type:

=COUNTBYCOLOR(A1:A10,36,FALSE)
(or =COUNTBYCOLOR(A1:A10,36) which is equivalent)

That way you'll know how many cells in range A1:A10 are colored in
Lightyellow.

Typing =COUNTBYCOLOR(A1:A10,3,FALSE) as you did, would tell you how many
plain red cells you have.


---------------------------------------------------------
Please reply to the newsgroup, and within the same thread.
Merci de répondre au groupe, et dans l'enfilade.

--
Bernard Rey - Toulouse / France
MVP - office:macintosh http://faq.office.macintosh.free.fr


Francis Hookam wrote :

 

[Bernard Rey]

Francis Hookam wrote :

Thanks Bernard - so we can only count the coloured cells but not sum the
values in the cells of a particular colour, which is what I really need -
pity - I shall have to write a macro which can be whistled up easily - maybe
running from "auto_open"

You simply picked the wrong macro then ;-)

On the very same Chip Pearson page, you'll fin the "Summing The Values Of
Cells With A Specific Color" macro (in fact, the next macro). And this one
will do exactly what you think it would...

http://cpearson.com/excel/colors.htm