Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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. jct wrote: 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 |
#3
![]() |
|||
|
|||
![]()
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 "Dave Peterson" wrote: 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. jct wrote: 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 |
#4
![]() |
|||
|
|||
![]()
Or better yet... to change the current cell to the color found within the
range...?? Thx "jct" wrote: 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 "Dave Peterson" wrote: 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. jct wrote: 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 |
#5
![]() |
|||
|
|||
![]()
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) jct wrote: 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 "Dave Peterson" wrote: 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. jct wrote: 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 -- Dave Peterson |
#6
![]() |
|||
|
|||
![]()
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. jct wrote: Or better yet... to change the current cell to the color found within the range...?? Thx "jct" wrote: 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 "Dave Peterson" wrote: 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. jct wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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) |