## Formulas based on background colour of cell

luv2bounce
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
You do not have the required permissions to view the files attached to this post.

HansV
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

luv2bounce
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

HansV
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"?
Best wishes,
Hans

luv2bounce
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

luv2bounce
StarLounger
Posts: 73
Joined: 07 Dec 2010, 12:21

### Re: Formulas based on background colour of cell

Basically the formula shown in B3

HansV
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?
Best wishes,
Hans

luv2bounce
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!

HansV
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.
Best wishes,
Hans

luv2bounce
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.

HansV
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:

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
End If
Next cell
End Function
``````
You can then use CountShaded(\$F8:\$BJ8) in the formula in F8.
Best wishes,
Hans

luv2bounce
StarLounger
Posts: 73
Joined: 07 Dec 2010, 12:21

### Re: Formulas based on background colour of cell

Amazing, amazing, amazing! Very grateful! Thank you!

luv2bounce
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!!!!!

HansV