+ Reply to Thread
Results 1 to 11 of 11

Counting cells colored by conditional formatting

  1. #1
    Registered User
    Join Date
    11-07-2019
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Counting cells colored by conditional formatting

    I'm using the gantt chart template made by Vertex. I'm trying to count how many cells with color by column. For some reason the VBA I found works on cells with conditional formatting outside the chart but not colored cells within the chart. I did some trial and error. If I use conditional formatting to fill cells with color when blank, it works. Attached is the template. Thank you.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    09-01-2018
    Location
    MISSOURI
    MS-Off Ver
    365
    Posts
    34

    Re: Counting cells colored by conditional formatting

    Sub DisplayFormatCount()

    Dim Rng As Range
    Dim CountRange As Range
    Dim ColorRange As Range
    Dim xBackColor As Long
    'On Error Resume Next

    Set CountRange = Range("$K$7:$BN$50")
    Set ColorRange = Range("$L$1")
    xReturn = 0
    For Each Rng In CountRange

    If Rng.DisplayFormat.Interior.Color = ColorRange.DisplayFormat.Interior.Color Then
    xBackColor = xBackColor + 1
    End If
    Next
    Range("$M$1").FormulaR1C1 = xBackColor
    End Sub

  3. #3
    Registered User
    Join Date
    11-07-2019
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Counting cells colored by conditional formatting

    Thank you for a quick response. How should I modify the VBA if I want to count by column? I would like to count the colored cells by day. Thank you.

  4. #4
    Registered User
    Join Date
    09-01-2018
    Location
    MISSOURI
    MS-Off Ver
    365
    Posts
    34

    Re: Counting cells colored by conditional formatting

    By changing range in below line
    Set CountRange = Range("$K$7:$K$50")
    but that would work only with one column

  5. #5
    Registered User
    Join Date
    11-07-2019
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Counting cells colored by conditional formatting

    Yes, it works. Can I make each corresponding day calculation to show on K1, L1, M1 and so on until column BN1? Thank you.

  6. #6
    Registered User
    Join Date
    09-01-2018
    Location
    MISSOURI
    MS-Off Ver
    365
    Posts
    34

    Re: Counting cells colored by conditional formatting

    I am pretty sure you can but not with macro i got unless you rerepeat macro for all other columns but that would be 56 times
    hopefully somebody who knows more then i do will help
    sorry

  7. #7
    Registered User
    Join Date
    11-07-2019
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Counting cells colored by conditional formatting

    Thank you for the help. At least now it is counting the colored cells. Have a great weekend

  8. #8
    Registered User
    Join Date
    09-01-2018
    Location
    MISSOURI
    MS-Off Ver
    365
    Posts
    34

    Re: Counting cells colored by conditional formatting

    welcome
    I hope you get an answer from somebody

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,881

    Re: Counting cells colored by conditional formatting

    Perhaps this formula based proposal will help:
    Paste the following formula into cell K1 and then drag the fill handle over to cell BN1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Registered User
    Join Date
    11-07-2019
    Location
    USA
    MS-Off Ver
    2007
    Posts
    9

    Re: Counting cells colored by conditional formatting

    This works great as well. Thank you.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,881

    Re: Counting cells colored by conditional formatting

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count colored cells by conditional formatting
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-13-2018, 03:01 AM
  2. Adding value of colored cells (conditional formatting)
    By Crawfy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-30-2016, 07:19 AM
  3. Replies: 4
    Last Post: 01-19-2016, 10:24 PM
  4. Replies: 1
    Last Post: 11-09-2013, 02:43 AM
  5. [SOLVED] Counting Cells Colored by a Conditional Format
    By Mike001 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-25-2013, 06:06 AM
  6. counting colored cells while using contitional formatting
    By okg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2011, 02:50 PM
  7. conditional formatting and colored cells
    By henro8 in forum Excel General
    Replies: 9
    Last Post: 07-10-2008, 03:12 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1