Sum Coloured Cells in Excel 2

[degsy70]

Hi,<br>
<br>
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 &quot;4 cells are green, 5 cells are red&quot; etc<br>
<br>
Any ideas greatly appreciated<br>
<br>
Des

 

[hotwyre]

Hello!<br>
<br>
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.<br>
This is still theoretical, I still have to check this one out to prove my insight.<br>
Hope this helps!

 

[degsy70]

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

 

[Neddy]

Yes, you need some code. This is a very simple coloured-cells counter which works; you can hack it to suit your own needs.<br>
<br>
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.<br>
<br>
Now create a new macro, and paste the following code into it. Get rid of the sub title and endsub lines.<br>
<br>
Sub CountColour()<br>
'<br>
' CountColour Macro<br>
' Macro recorded 21/02/2000 by PJJ<br>
'<br>
' Keyboard Shortcut: Ctrl+l<br>
'<br>
Dim RedCells, GreenCells, BlueCells, YellowCells As Variant<br>
Dim Colour As Variant<br>
Dim Counter As Integer<br>
<br>
For Counter = 1 To 10<br>
Colour = Worksheets(&quot;Sheet1&quot.Cells(Counter, 1).Interior.ColorIndex <br>
Select Case Colour<br>
Case 3<br>
RedCells = RedCells + 1<br>
Case 4<br>
GreenCells = GreenCells + 1<br>
Case 5<br>
BlueCells = BlueCells + 1<br>
Case 6<br>
YellowCells = YellowCells + 1<br>
End Select<br>
Next<br>
<br>
Worksheets(&quot;Sheet1&quot.Range(&quot;B1&quot.Value = RedCells<br>
Worksheets(&quot;Sheet1&quot.Range(&quot;B2&quot.Value = GreenCells<br>
Worksheets(&quot;Sheet1&quot.Range(&quot;B3&quot.Value = BlueCells<br>
Worksheets(&quot;Sheet1&quot.Range(&quot;B4&quot.Value = YellowCells<br>
<br>
End Sub<br>
<br>
When you run this, Cells B1 to B4 will show a count of the number of cells of each colour.

 

[degsy70]

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<br>
<br>
Much obliged

 

[Neddy]

Yeah, like this:<br>
<br>
The following line:<br>
<br>
Colour = Worksheets(&quot;Sheet1&quot.Cells(Counter, 1).Interior.ColorIndex <br>
<br>
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).<br>
<br>
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.<br>
<br>
Set up a new worksheet, colour the cells in Col A as before, then do the same in Col B. Then run this macro.<br>
<br>
Sub CountColours()<br>
'<br>
' CountColours Macro<br>
' Macro written 21/02/2000 by PJJ<br>
'<br>
' Keyboard Shortcut: Ctrl+l<br>
'<br>
Dim RedCells, GreenCells, BlueCells, YellowCells As Variant<br>
Dim Colour As Variant<br>
Dim CellCounter, ColumnCounter As Integer<br>
<br>
For ColumnCounter = 1 To 2<br>
For CellCounter = 1 To 10<br>
Colour = Worksheets(&quot;Sheet1&quot.Cells(CellCounter, ColumnCounter).Interior.ColorIndex<br>
Select Case Colour<br>
Case 3<br>
RedCells = RedCells + 1<br>
Case 4<br>
GreenCells = GreenCells + 1<br>
Case 5<br>
BlueCells = BlueCells + 1<br>
Case 6<br>
YellowCells = YellowCells + 1<br>
End Select<br>
Next<br>
Next<br>
<br>
Worksheets(&quot;Sheet1&quot.Range(&quot;C1&quot.Value = RedCells<br>
Worksheets(&quot;Sheet1&quot.Range(&quot;C2&quot.Value = GreenCells<br>
Worksheets(&quot;Sheet1&quot.Range(&quot;C3&quot.Value = BlueCells<br>
Worksheets(&quot;Sheet1&quot.Range(&quot;C4&quot.Value = YellowCells<br>
<br>
<br>
End Sub<br>
<br>

 

[degsy70]

Cheers, problem solved, much appreciated<br>
<br>
Degsy