Counting cells

[JRolph]

Conditional formatting gives me the lowest single number in a column and changes the fill color to gray. How can I count these cells across a range (G3:X60)?

 

[NateO]

This may be worth a gander:

http://www.mrexcel.com/board/viewtopic.php?topic=1585&forum=2


Hope this helps,

Cheers, Nate

 

[JRolph]

NateO
I entered the function you recommended and the formula. It returns the number of cells in the entire range. Was there something in the function I was supposed to change to match my criteria?

 

[Aladin Akyurek]

On 2002-03-11 18:44, JRolph wrote:
Conditional formatting gives me the lowest single number in a column and changes the fill color to gray. How can I count these cells across a range (G3:X60)?

Are you trying to determine/compute how often the lowest number occur in G3:X60?

Aladin

 

[JRolph]

I am trying to count the total number of cells in this range that have been changed to gray by conditional formatting.

 

[NateO]

Yep, it's not recognizing conditional formatting, the function thinks the reference cell interior is clear (xlblank).


Nate
This message was edited by NateO on 2002-03-13 09:51

 

[Aladin Akyurek]

On 2002-03-13 08:59, JRolph wrote:
I am trying to count the total number of cells in this range that have been changed to gray by conditional formatting.

What is the formula that you use to turn them gray?

If it's some VBA code that makes them gray, I'd suggest to turn that code into one that can count what it turns to gray. But, that would be something for the VBA army around here.

 

[NateO]

Sorry, I deleted the last post because I decided we can have our cake and eat it too! Two out three of these functions are Chip Pearson's, bright guy..

Paste the following functions in a normal vba module:

Function CntCondCol(InputRange As Range, ColorRange As Range) As Double
Dim cl As Range, TempCount As Double, ColorIndex As Integer
Application.Volatile
If ActiveCondition(ColorRange)<> 0 Then
ColorIndex = ColorIndexOfCF(ColorRange)
Else: ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
End If
TempCount = 0
On Error Resume Next
For Each cl In InputRange.Cells
If ColorIndexOfCF(cl) = ColorIndex Then TempCount = TempCount + 1
Next cl
On Error GoTo 0
Set cl = Nothing
CntCondCol = TempCount
End Function

Function ActiveCondition(Rng As Range) As Integer
Dim Ndx As Long
Dim FC As FormatCondition
If Rng.FormatConditions.Count = 0 Then
ActiveCondition = 0
Else
For Ndx = 1 To Rng.FormatConditions.Count
Set FC = Rng.FormatConditions(Ndx)
Select Case FC.Type
Case xlCellValue
Select Case FC.Operator
Case xlBetween
If CDbl(Rng.Value) >= CDbl(FC.Formula1) And _
CDbl(Rng.Value)<= CDbl(FC.Formula2) Then
ActiveCondition = Ndx
Exit Function
End If
Case xlGreater
If CDbl(Rng.Value) > CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Case xlEqual
If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Case xlGreaterEqual
If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Case xlLess
If CDbl(Rng.Value)< CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Case xlLessEqual
If CDbl(Rng.Value)<= CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Case xlNotEqual
If CDbl(Rng.Value)<> CDbl(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Case xlNotBetween
If CDbl(Rng.Value)<= CDbl(FC.Formula1) Or _
CDbl(Rng.Value) >= CDbl(FC.Formula2) Then
ActiveCondition = Ndx
Exit Function
End If
Case Else
Debug.Print "UNKNOWN OPERATOR"
End Select
Case xlExpression
If Application.Evaluate(FC.Formula1) Then
ActiveCondition = Ndx
Exit Function
End If
Case Else
Debug.Print "UNKNOWN TYPE"
End Select
Next Ndx
End If
ActiveCondition = 0
End Function

Function ColorIndexOfCF(Rng As Range, _
Optional OfText As Boolean = False) As Integer
Dim AC As Integer
AC = ActiveCondition(Rng)
If AC = 0 Then
If OfText = True Then
ColorIndexOfCF = Rng.Font.ColorIndex
Else
ColorIndexOfCF = Rng.Interior.ColorIndex
End If
Else
If OfText = True Then
ColorIndexOfCF = Rng.FormatConditions(AC).Font.ColorIndex
Else
ColorIndexOfCF = Rng.FormatConditions(AC).Interior.ColorIndex
End If
End If
End Function



Now, place the following formula in a cell of your choice:

=CntCondCol(G3:X60,A1)

Change A1 to a cell reference that has the same shading that you want to count. The beauty of this is that the reference cell (in this case a1) can either be shaded by conditional formatting or manual/standard formatting.

This works pretty well on xl2000. Hope this helps. Cheers,

Nate
This message was edited by nateo on 2002-03-13 10:07

 

[JRolph]

I must have missed something. It still totals the entire range of cells!