Formulas based on background colour of cell

 StarLounger
 Posts: 73
 Joined: 07 Dec 2010, 12:21
Formulas based on background colour of cell
I have a spreadsheet that will be used for a shaded Gantt chart. I need to be able to count the number of cells in any row that are shaded. And I would like cells that are shaded to include a formula where a number is divided by the count of total shaded cells in the row.
I have been googling and created a name to return the colour number of the shaded cell  which I have called Gantt colour, but I can't figure out how to create a formula that harnesses the colour information.
I've simplified an example of what I'm trying to do.
I wonder if anyone has suggestions for how I can do this?
Many thanks in anticipation!
Cheers
Susan
I have been googling and created a name to return the colour number of the shaded cell  which I have called Gantt colour, but I can't figure out how to create a formula that harnesses the colour information.
I've simplified an example of what I'm trying to do.
I wonder if anyone has suggestions for how I can do this?
Many thanks in anticipation!
Cheers
Susan
You do not have the required permissions to view the files attached to this post.

 Administrator
 Posts: 79321
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Formulas based on background colour of cell
Your formula uses a function ColorIn that is not available in the workbook.
Best wishes,
Hans
Hans

 StarLounger
 Posts: 73
 Joined: 07 Dec 2010, 12:21
Re: Formulas based on background colour of cell
Ah, sorry, thought it would copy that too: The colorIn formula is:
Function ColorIn(color As Range) As Integer
ColorIn = color.Interior.ColorIndex
End Function
Function ColorIn(color As Range) As Integer
ColorIn = color.Interior.ColorIndex
End Function

 Administrator
 Posts: 79321
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Formulas based on background colour of cell
Thanks.
You mention "a formula where a number is divided by the count of total shaded cells in the row."
What is "a number"?
You mention "a formula where a number is divided by the count of total shaded cells in the row."
What is "a number"?
Best wishes,
Hans
Hans

 StarLounger
 Posts: 73
 Joined: 07 Dec 2010, 12:21
Re: Formulas based on background colour of cell
The number would be from the column in D  est MM CPI

 StarLounger
 Posts: 73
 Joined: 07 Dec 2010, 12:21
Re: Formulas based on background colour of cell
Basically the formula shown in B3

 Administrator
 Posts: 79321
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Formulas based on background colour of cell
Column D contains references to another workbook, so I cannot do anything with it.
Can you provide an example of what you would want the result to be in for example G8 and H8?
Can you provide an example of what you would want the result to be in for example G8 and H8?
Best wishes,
Hans
Hans

 StarLounger
 Posts: 73
 Joined: 07 Dec 2010, 12:21
Re: Formulas based on background colour of cell
So sorry! I really was trying to help, being that I asked for help!
column D brings in the total manmonths of effort for the complete task from another sheet  these could just be random numbers for the time being. The formula that would go in the shaded cells would be (say for row x):
=IF($dx>0, $dx/$ex,"")  where x would be the row number
This formula should only apply to shaded cells as the vertical totals for each row are then added to give a resource profile.
Hopefully that makes sense?
Many thanks as always for your help!
column D brings in the total manmonths of effort for the complete task from another sheet  these could just be random numbers for the time being. The formula that would go in the shaded cells would be (say for row x):
=IF($dx>0, $dx/$ex,"")  where x would be the row number
This formula should only apply to shaded cells as the vertical totals for each row are then added to give a resource profile.
Hopefully that makes sense?
Many thanks as always for your help!

 Administrator
 Posts: 79321
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Formulas based on background colour of cell
Try this in F8:
=IF(GanttColour, IF($D8>0, $D8/$E8, ""), "")
Copy F8 and paste as formula to F8:BJ12 and to F14:BJ23.
=IF(GanttColour, IF($D8>0, $D8/$E8, ""), "")
Copy F8 and paste as formula to F8:BJ12 and to F14:BJ23.
Best wishes,
Hans
Hans

 StarLounger
 Posts: 73
 Joined: 07 Dec 2010, 12:21
Re: Formulas based on background colour of cell
Hi Hans!
WooHoo! Thank you. That works for putting the formula in, but I don't know how to get it to count the number of shaded cells in the row for column D. Do you have any suggestions? I tried COUNTIF(F8:T8, GanttColour), but it says that is a circular reference.
WooHoo! Thank you. That works for putting the formula in, but I don't know how to get it to count the number of shaded cells in the row for column D. Do you have any suggestions? I tried COUNTIF(F8:T8, GanttColour), but it says that is a circular reference.

 Administrator
 Posts: 79321
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands
Re: Formulas based on background colour of cell
Create the following function in a module in the Visual Basic Editor:
You can then use CountShaded($F8:$BJ8) in the formula in F8.
Code: Select all
Function CountShaded(rng As Range) As Long
Dim cell As Range
For Each cell In rng
If cell.Interior.ColorIndex <> xlColorIndexNone Then
CountShaded = CountShaded + 1
End If
Next cell
End Function
Best wishes,
Hans
Hans

 StarLounger
 Posts: 73
 Joined: 07 Dec 2010, 12:21
Re: Formulas based on background colour of cell
Amazing, amazing, amazing! Very grateful! Thank you!

 StarLounger
 Posts: 73
 Joined: 07 Dec 2010, 12:21
Re: Formulas based on background colour of cell
I really can't thank you enough! This is brilliant!!!!!

 Administrator
 Posts: 79321
 Joined: 16 Jan 2010, 00:14
 Status: Microsoft MVP
 Location: Wageningen, The Netherlands