I am using the current version 4.7.2 (April 2011)
I have never applied the function to CF cells but only to ones I have
manually set.
May work if recalc twice in succession.
Thanks everyone for all your help and assistance on this....i am not
an excel expert so wanted soemthing like an idiots guide lol.......
I did however manage to find the answer to my question purely bu
chance and I have reporduced it below for info....
There are no built-in functions to use in a formula that can detect the "filled in" color of a cell. You would have to use a custom made function like below.
If you want to count the cells with filled in background color in say range A1 to B20
=CountFilled(A1:B20)
To install this custom function:
Alt+F11 to open the VB editor
On the menu select Insert\Module
Paste the code below in the VB edit window
---
Function CountFilled(rng As Range) As Long
Dim Cell As Range
Application.Volatile True
For Each Cell In rng
If Cell.Interior.ColorIndex <> -4142 Then
CountFilled = CountFilled + 1
End If
Next Cell
End Function
I have another challenge if you are up for it lol
I have a list of numbers which I would like to automatically format
with a green background......does anyone know how I can do this. The
start of the top four values will always be indicated by rthe last
number in the set being greater than the first number in the next
set.....in the first example below rthe set ends at 16 as the next
number is 0.8 etc
3.5
4
5
8
8 the first four values should be highlighted green - so in this
case it would be 5 numbers as these are the first four values, 8 being
selected twice
10
12
14
14
14
16
0.8 the next four value should be highlighted green
4
6.5
8
14
16
the next four and so on..........the top four values are identified by
the number to start with being smaller than the preceding number
1.2
3
3.5
8
12
2.5 From here top four values to be green
3
3.5
5.5
7.5
10
20
2.75 From here top four values to be green
3.33
5
6.5
2.75 From here top four values to be green
3
4.5
6
8
10
10
33
2.75 From here top four values to be green
3.25
5.5
10
10
12
12
12
14
16
2 From here top four values to be green
7
4.5
6.5
8
10
10
I hope this makes sense and any help appreciated - i could send the
spreadsheet if it helps
thanks
Sam
