×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sum Coloured Cells in Excel
2

Sum Coloured Cells in Excel

Sum Coloured Cells in Excel

(OP)
Hi,

Does anyone know how I can sum a range of cells in Excel that are coloured i.e. i want it to select a range and then say "4 cells are green, 5 cells are red" etc

Any ideas greatly appreciated

Des

RE: Sum Coloured Cells in Excel

Hello!

I am not sure about this one, but here goes nothing. I think the only solution for this one is to create a macro. Wherein this macro can be activated thru an option in the menu bar. The algo will be, search for a particular range of cells then check out the color of it, then one by one add it then get the some.
This is still theoretical, I still have to check this one out to prove my insight.
Hope this helps! ;)

RE: Sum Coloured Cells in Excel

(OP)
I know this but i don't know what the algorithm would be, would I need to write some VB code?

RE: Sum Coloured Cells in Excel

2
Yes, you need some code. This is a very simple coloured-cells counter which works; you can hack it to suit your own needs.

First, open a new spreadsheet. Change the interior colours of the cells as follows: A1 to A4 - Red. A5 to A7 - Green. A8 to A9 - Blue. A10 - Yellow. NB These are defined colours in an Excel property called ColorIndex, and their values are 3, 4, 5 and 6 respectively. Make sure you get the right colours (hold the mouse over the relevant square of the colour palette to get the pop-up description) or the code won't work.

Now create a new macro, and paste the following code into it. Get rid of the sub title and endsub lines.

Sub CountColour()
'
' CountColour Macro
' Macro recorded 21/02/2000 by PJJ
'
' Keyboard Shortcut: Ctrl+l
'
Dim RedCells, GreenCells, BlueCells, YellowCells As Variant
Dim Colour As Variant
Dim Counter As Integer

For Counter = 1 To 10
Colour = Worksheets("Sheet1").Cells(Counter, 1).Interior.ColorIndex
Select Case Colour
Case 3
RedCells = RedCells + 1
Case 4
GreenCells = GreenCells + 1
Case 5
BlueCells = BlueCells + 1
Case 6
YellowCells = YellowCells + 1
End Select
Next

Worksheets("Sheet1").Range("B1").Value = RedCells
Worksheets("Sheet1").Range("B2").Value = GreenCells
Worksheets("Sheet1").Range("B3").Value = BlueCells
Worksheets("Sheet1").Range("B4").Value = YellowCells

End Sub

When you run this, Cells B1 to B4 will show a count of the number of cells of each colour.

RE: Sum Coloured Cells in Excel

(OP)
Thanks I've got that to work but do you know how I can get it to look at column A 1 to 10 and then go on to column B 1 to 10 etc

Much obliged

RE: Sum Coloured Cells in Excel

Yeah, like this:

The following line:

Colour = Worksheets("Sheet1").Cells(Counter, 1).Interior.ColorIndex

contains a reference to Excel's Worksheets.Cells property. The .Cells property has two arguments - row number, column number. In the example I already gave you, row number was determined by the variable Counter, which altered through the range 1 to 10 as the For ... Next loop ran. Column number, however, was static (1).

The following example shows how, by putting in another For...Next loop to increment a variable called ColumnCounter, you can make Excel loop through columns as required.

Set up a new worksheet, colour the cells in Col A as before, then do the same in Col B. Then run this macro.

Sub CountColours()
'
' CountColours Macro
' Macro written 21/02/2000 by PJJ
'
' Keyboard Shortcut: Ctrl+l
'
Dim RedCells, GreenCells, BlueCells, YellowCells As Variant
Dim Colour As Variant
Dim CellCounter, ColumnCounter As Integer

For ColumnCounter = 1 To 2
For CellCounter = 1 To 10
Colour = Worksheets("Sheet1").Cells(CellCounter, ColumnCounter).Interior.ColorIndex
Select Case Colour
Case 3
RedCells = RedCells + 1
Case 4
GreenCells = GreenCells + 1
Case 5
BlueCells = BlueCells + 1
Case 6
YellowCells = YellowCells + 1
End Select
Next
Next

Worksheets("Sheet1").Range("C1").Value = RedCells
Worksheets("Sheet1").Range("C2").Value = GreenCells
Worksheets("Sheet1").Range("C3").Value = BlueCells
Worksheets("Sheet1").Range("C4").Value = YellowCells


End Sub

RE: Sum Coloured Cells in Excel

(OP)
Cheers, problem solved, much appreciated

Degsy

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close