# Formula dependent of cell colour

#### Kiwi den

##### Board Regular
Looking for assistance
I have a spreadsheet with column H from cells 1 - 750 populated by numbers.
I would like to total the cells in this column into cell (H:751) that have a cell colour "Green"

Can someone please point me for info on how I could do this please

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Fluff

##### MrExcel MVP, Moderator
How are the cells being coloured?

#### Kiwi den

##### Board Regular
How are the cells being coloured?
The cells are manually coloured when that row is entered

#### Fluff

##### MrExcel MVP, Moderator
In that case you will need VBA, a formula cannot see cell formats.

#### Kiwi den

##### Board Regular

In that case you will need VBA, a formula cannot see cell formats.
ok sounds good, can you point me to an example or where I can get this

#### Fluff

##### MrExcel MVP, Moderator
VBA Code:
``````Function SumByColor(SumRange As Range, ColourCell As Range) As Double
Dim Cl As Range
Dim Colr As Long
Application.Volatile

Colr = ColourCell.Interior.Color
For Each Cl In SumRange
If Cl.Interior.Color = Colr Then
SumByColor = SumByColor + Cl.Value
End If
Next TCell
End Function``````
Used like
Excel Formula:
``=SumByColor(H1:H170,G1)``
where H1:H170 is the range to sum & G1 has the colour you're interested in.
However this will only recalculate when the sheet recalcs & not when you change the colour of a cell.

#### Kiwi den

##### Board Regular
VBA Code:
``````Function SumByColor(SumRange As Range, ColourCell As Range) As Double
Dim Cl As Range
Dim Colr As Long
Application.Volatile

Colr = ColourCell.Interior.Color
For Each Cl In SumRange
If Cl.Interior.Color = Colr Then
SumByColor = SumByColor + Cl.Value
End If
Next TCell
End Function``````
Used like
Excel Formula:
``=SumByColor(H1:H170,G1)``
where H1:H170 is the range to sum & G1 has the colour you're interested in.
However this will only recalculate when the sheet recalcs & not when you change the colour of a cell.
Thanks I will give this a try

Ok,

Replies
3
Views
69
Replies
3
Views
75
Replies
1
Views
51
Replies
3
Views
47
Replies
3
Views
187

1,127,216
Messages
5,623,446
Members
415,974
Latest member
ZorroOP

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back