Counting cells
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Counting cells

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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)?

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This may be worth a gander:

    http://www.mrexcel.com/board/viewtop...c=1585&forum=2


    Hope this helps,

    Cheers, Nate

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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


  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,775
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    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.

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    New Member
    Join Date
    Mar 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com