Hi
I need to count cells that display a pattern xlsemigray75 in a range(eg
c3:c300)
any ideas would be appreciated
Steve


--
Steve1154
------------------------------------------------------------------------
Steve1154's Profile: http://www.excelforum.com/member.php...o&userid=31550
View this thread: http://www.excelforum.com/showthread...hreadid=512457

Steve1154 wrote:
Hi
I need to count cells that display a pattern xlsemigray75 in a range(eg
c3:c300)
any ideas would be appreciated
Steve


Copy this function into a module

Function countGray75(rng As Range) As Long
Dim c As Range
countGray75 = 0
For Each c In rng
If c.Interior.Pattern = xlPatternGray75 Then
countGray75 = countGray75 + 1
End If
Next
End Function

Then use the formula =countGray75(C3:C300) in your spreadsheet
Of course you can use any range

Since the custom function updates when a cell in that range has its data
modified (not it's pattern), you may need to just edit a cell in the
range of your function and just press enter. This will trigger off the
custom function to calculate.

Some strange behavior by excel;
Using format painter tool anywhere on sheet, function calculated
Using Fill color tool anywhere on sheet, function DID NOT calculate

Even making the function volatile didn't help when using the fill color
tool.

Well, i hope it helps

George

That works a treat George. The function runs without any need for data
to be added! And with the use of custom function you have given me an
insight into solving another couple of Problems Cheers
Steve


--
Steve1154
------------------------------------------------------------------------
Steve1154's Profile: http://www.excelforum.com/member.php...o&userid=31550
View this thread: http://www.excelforum.com/showthread...hreadid=512457