Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reminder notifications in a column | Excel Discussion (Misc queries) | |||
Excel won't copy outside sheet | Excel Discussion (Misc queries) | |||
Copy worksheets with formulas between different workbooks | Excel Discussion (Misc queries) | |||
Copy Function | Excel Discussion (Misc queries) | |||
Copy and pasting graphs to PowerPoint | Excel Discussion (Misc queries) |