Results 1 to 6 of 6

Counting coloured cells

Threaded View

  1. #1
    Registered User
    Join Date
    10-24-2012
    Location
    Guelph, ON
    MS-Off Ver
    Excel 2010
    Posts
    41

    Counting coloured cells

    Hi: I've been looking through various threads and other sites and can't figure this one out. I'm trying to count cells in a column based on their font colour which are defined by conditional formatting in the spreadsheet. I'm using 2010 and think that maybe the macro I'm trying to use won't work in 2010.

    In column R from R9 to the last row (which I put "end" into) I want to count colour coded dates that are for previous, current and future months. Future dates are in red, current are black and previous are blue. I want to return the counts in cells V2, V3, V4. Hopefully that makes sense??

    My current macro will return the full count in V3 which is "black". So it's not differentiating the colours. I've also attached a sample file with our sensitive data deleted. Here is what I have:

    Sub Color()

    Dim previous As Integer
    Dim future As Integer
    Dim current As Integer

    Range("r9").Select
    future = 0
    current = 0
    previous = 0

    Do Until Selection.Value = "end"
    If Selection.Font.Color = RGB(255, 0, 0) Then
    future = future + 1
    End If
    Selection.Offset(1, 0).Select
    Loop

    Range("r9").Select
    Do Until Selection.Value = "end"
    If Selection.Font.Color = RGB(0, 0, 255) Then
    previous = previous + 1
    Else
    current = current + 1
    End If
    Selection.Offset(1, 0).Select
    Loop

    Range("v2").Select
    Selection.Value = future

    Range("v3").Select
    Selection.Value = current

    Range("v4").Select
    Selection.Value = previous

    End Sub

    Any suggestions or help would be greatly appreciated!
    Thanks!!! Stef.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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