Excel and colours

[Dan]

Hi, need some advice.

I have created an Excel document and i need to automate it.

Each cell along a row has a value (0.5,1,1.5, etc,) at the
end of that row is the cumulative value of the cells. The
document is updated on a weekly basis and to denote
progress a colour from the FILL COLOUR command is added to
a completed cell.

Is there any way that by clouring a cell Excel can
recognize a coloured cell as being complete and adding the
value of a cell to the value in the cumulative value.

All advice would be greatly appreciated!

Dan

 

[tur13o]

Unless you want to use VBA - can you insert a row above or below your data
row that is used to
indicate that the cell directly below or above has in it completed data and
thus should be included
in your cumulative total ? If you don't mind this then you could us the
sumif function to clac you total
and auto format to colour the completed data element (if that is still
important for display purposes).

 

[L. Howard Kittle]

Hi Dan,

Give this a try.

Sub SumSome()
Dim Cell As Range
Dim i As Integer

For Each Cell In Range("Data")
If Cell.Interior.ColorIndex <> xlNone Then
i = i + Cell.Value
End If
Next

Range("H1").Value = i
End Sub

Where the named range, Data, in this example is A1:F1 and the total goes
into H1.

HTH
Regards,
Howard