+ Post New Thread
Results 1 to 8 of 8
Office Software Thread, Counting coloured cells in MS Excel 2010 in Technical; Hi I was wondering if anybody could give me a helping hand please. I am exporting data from our SIMS ...
  1. #1

    Join Date
    Feb 2013
    Location
    Buckinghamshire
    Posts
    8
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Counting coloured cells in MS Excel 2010

    Hi
    I was wondering if anybody could give me a helping hand please. I am exporting data from our SIMS into Excel and rather than counting *, =, +, - etc. I would like to count the coloured background of those cells. I can filter the data by the coloured background, but I don't know how to count them (other than manually, obviously).
    Grateful for any help!
    Thanks

  2. #2

    bladedanny's Avatar
    Join Date
    May 2009
    Location
    Sheffield
    Posts
    1,261
    Thank Post
    187
    Thanked 296 Times in 222 Posts
    Rep Power
    129
    If it's only a quick count, after filtering if you highlight all the cells it should give a summary of stats including count.

    If you want it permanently some form of COUNTIF() could be used.

  3. #3

    Join Date
    Feb 2013
    Location
    Buckinghamshire
    Posts
    8
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Hi bladedanny

    I was going for the COUNTIF, but when when I tell Excel to look for a = (equal sign) within the designated range, it won't accept that. Now if I could tell Excel to count the number of red coloured cells, that would solve my problem.

  4. #4
    VeryPC_Tom_M's Avatar
    Join Date
    Aug 2012
    Location
    Sheffield
    Posts
    388
    Thank Post
    14
    Thanked 82 Times in 79 Posts
    Rep Power
    44
    the only real way is to get VBA involved..

    Here's a website with a bunch of colour functions for Excel 2003 - Color Functions In Excel

  5. #5

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by QWERTY72 View Post
    I was going for the COUNTIF, but when when I tell Excel to look for a = (equal sign) within the designated range, it won't accept that. Now if I could tell Excel to count the number of red coloured cells, that would solve my problem.
    Don't overcomplicate things, count the symbols if you have them.

    Code:
    To count "=":
    =COUNTIF(RANGE,"==")
    
    To count "+":
    =COUNTIF(RANGE,"=+")
    
    To count "-":
    =COUNTIF(RANGE,="=-")

  6. Thanks to LosOjos from:

    QWERTY72 (4th March 2013)

  7. #6

    Join Date
    Feb 2013
    Location
    Buckinghamshire
    Posts
    8
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    I don't know where to start with VBA - it looks terribly complicated.I'm just using the standard colours red, yellow, dark and light green.

  8. #7

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by QWERTY72 View Post
    I don't know where to start with VBA - it looks terribly complicated.I'm just using the standard colours red, yellow, dark and light green.
    If you must count by colour (and I don't recommend it, see my previous post for Excel formulas that will count the symbols), then you'll want to add this code to a module in your workbook:

    Code:
    Public Function cell_colour(Cell_Check As Range) As Long
    cell_colour = Cell_Check.Interior.Color
    End Function
    
    Public Function count_colour(Count_Range As Range, Colour As Long) As Long
    Dim x As Range
    count_colour = 0
    For Each x In Count_Range
        If x.Interior.Color = Colour Then
            count_colour = count_colour + 1
        End If
    Next x
    End Function
    Then you can use those functions in your worksheet like any other excel function, like so:

    Code:
    =COUNT_COLOUR(RANGE, CELL_COLOUR(CELL))
    The range is the range of cells to count, and using the cell_colour function and highlighting a cell that is the same colour as that which you wish to count will get the colour code (simpler than working it out every time)

  9. Thanks to LosOjos from:

    QWERTY72 (11th September 2013)

  10. #8

    Join Date
    Feb 2013
    Location
    Buckinghamshire
    Posts
    8
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    This works fine, thank you! But I will try the colour counting as well - been curious about it for quite some time.

SHARE:
+ Post New Thread

Similar Threads

  1. colour a cell in excel for a time period
    By schugh in forum Coding
    Replies: 1
    Last Post: 20th April 2012, 09:45 AM
  2. [MS Office - 2010] Access 2010 - locking a cell in a table/form after text is entered
    By fred54 in forum Office Software
    Replies: 7
    Last Post: 25th March 2011, 03:52 PM
  3. MS Office 2010 Beta installed in French
    By speckytecky in forum Office Software
    Replies: 9
    Last Post: 17th March 2010, 06:12 PM
  4. VB code to colour cells in a word table
    By park_bench in forum Coding
    Replies: 4
    Last Post: 30th June 2008, 08:52 AM
  5. RANDBETWEEN in MS Excel
    By woody in forum Educational Software
    Replies: 3
    Last Post: 14th February 2006, 01:34 PM

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Posting Permissions

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