Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Counting Formatted Cells (97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> but the Search function is disabled and I need to know if there is a way to count cells formatted via a Conditional Format?

    If there is a way using some sort of a formula, I can do it in VBA, let me know.

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> VERY MUCH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting Formatted Cells (97 and >)

    It is NOT real simple, but then it is not completely terrible either. You must actually check for a condition and then your code must determine which conditions are in affect, and then what the formatting for that condition is.

    This should get you started. It is a link to Chip Pearson's page VB code with cond formatting:
    http://www.cpearson.com/excel/CFColors.htm

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting Formatted Cells (97 and >)

    If you want to just count the number of cells with any conditional format, something like (untested):

    Function CountCondFormat() as Long
    Dim rngCell as Range
    For Each rngCell In Selection
    If rngCell.FormatConditions.Count > 0 Then CountCondFormat = CountCondFormat +1
    Next rngCell
    End Function
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Counting Formatted Cells (97 and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Steve

    I have noticed something very <font color=red>BAD </font color=red>. It seems that during the renovations on the Lounge, and sections of the Lounge, some functions have been turned off.

    I have not been getting updates that a response has been added to the thread, and I also have not been getting any digest messages, since May 15th?!

    But Thanks for replying to my message with a solution.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Counting Formatted Cells (97 and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> John

    This is what I ended up using. Did you see my reply to Steve, well here it is, any idea when we will have these functions back online?

    I have noticed something very <font color=red>BAD </font color=red>. It seems that during the renovations on the Lounge, and sections of the Lounge, some functions have been turned off.

    I have not been getting updates that a response has been added to the thread, and I also have not been getting any digest messages, since May 15th?!

    But Thanks for replying to my message with a solution.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting Formatted Cells (97 and >)

    The Lounge is being reworked, and there are probably a few things that are temporarily disabled. BTW, don't thank me too much, when I came to see what you were replying about I realized that using .Selection in a Function could be a bit of a trick! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting Formatted Cells (97 and >)

    Since you didn't "solve" the problem you raised:
    This might be better (to give the function the range to count in):

    <pre>Function CountCondFormat(rng as range) as Long
    Dim rngCell as Range
    For Each rngCell In rng
    If rngCell.FormatConditions.Count > 0 Then CountCondFormat = CountCondFormat +1
    Next rngCell
    End Function</pre>


  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Counting Formatted Cells (97 and >)

    Hi Wassim,
    Re your notifications, have you checked your subscription settings under Controls? I believe a while back Claude was getting a lot of emails bounced for various reasons and he had to turn some people's notifications off.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Counting Formatted Cells (97 and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> rory

    I checked all my settings, and now I am back to normal, or there I think I am back to normal.

    So I'll see what goes on in a few days and then get back to the Controls and see what stuck and what I still need to address.

    Thanks for your feedback.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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