Formula dependent of cell colour

Kiwi den

Board Regular
Joined
Feb 17, 2014
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,248
Office Version
  1. 365
Platform
  1. Windows
How are the cells being coloured?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,248
Office Version
  1. 365
Platform
  1. Windows
In that case you will need VBA, a formula cannot see cell formats.
 

Kiwi den

Board Regular
Joined
Feb 17, 2014
Messages
140
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
55,248
Office Version
  1. 365
Platform
  1. Windows
How about
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
Joined
Feb 17, 2014
Messages
140
Office Version
  1. 365
Platform
  1. Windows
How about
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,248
Office Version
  1. 365
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,126,978
Messages
5,621,942
Members
415,868
Latest member
Pita

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top