Summarize & copy color format

[Guest]

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

 

[Dave Peterson]

You could use a userdefined function that returns the color of the cell.

Chip Pearson has a nice version at:
http://www.cpearson.com/excel/colors.htm

I'd put that function in another cell on the same row and then do all the work
against that column. But Chip also shows how to sum cells based on the color,
too.

 

[Guest]

Thanks, Dave. I've added columns next door with the function, then put a
conditional format on the summarized line. Works fine if the color is in the
first cell of the range. If it's in another cell, with the first cell blank,
it returns white. I'm just learning VBA... This is the function I choose from
the link you provided:

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Function

Can you tell me what to modify to return the color index of any cell within
a given column range? If there are colored cells, there will only be one
color within a range.

Thanks much,
Janice

 

[Guest]

Or better yet... to change the current cell to the color found within the
range...??
Thx

 

[Dave Peterson]

Some minor modifications to Chip's code seems to work ok for me:

Option Explicit

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True

Dim myCell As Range

If OfText = True Then
CellColorIndex = xlAutomatic
Else
CellColorIndex = xlNone
End If

For Each myCell In InRange.Cells
If OfText = True Then
If myCell.Font.ColorIndex = xlAutomatic Then
'keep looking
Else
CellColorIndex = myCell.Font.ColorIndex
Exit For
End If
Else
If myCell.Interior.ColorIndex = xlNone Then
'keep looking
Else
CellColorIndex = myCell.Interior.ColorIndex
Exit For
End If
End If
Next myCell

End Function

And use it like:
=CellColorIndex(A1:A10)
or
=CellColorIndex(A1:A10,TRUE)

 

[Dave Peterson]

You can't have a udf change the colors of other cells (when you use that UDF in
a formula in a worksheet cell).

But you could run a macro that could do that kind of change. The bad news is
you'd have to give the rules to determine the ranges.