Formulas based on background colour of cell

[luv2bounce]

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

[HansV]

Your formula uses a function ColorIn that is not available in the workbook.

[luv2bounce]

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]

Thanks.
You mention "a formula where a number is divided by the count of total shaded cells in the row."
What is "a number"?

[luv2bounce]

The number would be from the column in D - est MM CPI

[luv2bounce]

Basically the formula shown in B3

[HansV]

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?

[luv2bounce]

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]

Try this in F8:
=IF(GanttColour, IF($D8>0, $D8/$E8, ""), "")
Copy F8 and paste as formula to F8:BJ12 and to F14:BJ23.

[luv2bounce]

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]

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
            CountShaded = CountShaded + 1
        End If
    Next cell
End Function

You can then use CountShaded($F8:$BJ8) in the formula in F8.

[luv2bounce]

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

[luv2bounce]

I really can't thank you enough! This is brilliant!!!!!

[HansV]

Glad to have been able to help!