Thanks 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
Francis Hookam said:
I have accurate figures and guesstimates in the same column - the
guesstimate cells are coloured
It would be a great advantage to sum the accurate figures and the guess
figures separately
Can you suggest a way - there might even be more than one colour so I might
have three countifs
One way:
http://cpearson.com/excel/colors.htm