Sum & copy color format...?
 

I have data with cells colored depending on certain criteria (not conditional
formating - this is by human hand). Is there a way to subtotal / group /
outline, or otherwise sum and carry over the cell color to the summarized
line?

It appears that color format in the line directly beneath the summarized
line carries over. What I want is for the summary to look at all cells being
totaled (within the same column) and find if any have a non-white
background... If so, then bring that color to the summarized cell. There will
be only one color within each section, but it may be 4 cells beneath the
summarized line.

I can provide a sample section, if anyone's interested.

Thanks in advance,
jct

PS - Should I post this to another section of the Excel group?? Thx.

Keeping it simple (but absorbing an additional column for the
assessment) you could define a simple function that analyses the
colorindex of the cells and returns a value that you could then use to
sort / group / conditional sum.

Something like that below placed in a VB Module would return you the
fill colour of the cell

Function myColorIndex(userRange As Range)
myColorIndex = userRange.Interior.colorIndex
End Function

exchange "Interior" for "Font " to assess the text colour.

You could make your function complete the sum or count (or make it
switchable via an extra passed variable) using a "For ... Each" loop
in the function to compelte the addition task.

An extra question for anyone else is : can you use a user defined
function nested into a useful summation function (such as CountIf
/SumIf or a conditional sum) so that an extra column is not needed?

hope this is of some help.


--
optionbase1
------------------------------------------------------------------------
optionbase1's Profile: http://www.excelforum.com/member.php...o&userid=25212
View this thread: http://www.excelforum.com/showthread...hreadid=386525