Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding colored cells only
I am wanting to add specifice colored cells only on my worksheet, the colors
represent stages that different orders are in and would like to keep a running dollar amount total in each stage - maybe a conditional format? I don't know how to tell excel to add only a certain color of cell - is it possible without buying an additional program? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding colored cells only
You need code for this
http://www.cpearson.com/excel/colors.aspx info on how to install macros and UDFs http://www.mvps.org/dmcritchie/excel/install.htm -- Regards, Peo Sjoblom "lindateammom" wrote in message ... I am wanting to add specifice colored cells only on my worksheet, the colors represent stages that different orders are in and would like to keep a running dollar amount total in each stage - maybe a conditional format? I don't know how to tell excel to add only a certain color of cell - is it possible without buying an additional program? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding colored cells only
Hi,
If the colours are as a result of a conditional format then naybe it coud be done with a formula but more detail qould be required. If the colours are simply fill colours then you need a UDF and the one below does that http://www.ozgrid.com/Excel/count-sum-cell-color.htm Mike "lindateammom" wrote: I am wanting to add specifice colored cells only on my worksheet, the colors represent stages that different orders are in and would like to keep a running dollar amount total in each stage - maybe a conditional format? I don't know how to tell excel to add only a certain color of cell - is it possible without buying an additional program? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding colored cells only
Hi,
You must use VBA, here is some code you could modify: Function SumFormats(R As Range, E As Range) As Integer Dim cell As Range Dim Total As Integer Dim T As Boolean Set S = E.Cells(1, 1) Total = 0 For Each cell In R T = True With cell If .Font.ColorIndex < S.Font.ColorIndex Then T = False If .Interior.ColorIndex < S.Interior.ColorIndex Then T = False If .Font.Bold < S.Font.Bold Then T = False If .Font.Italic < S.Font.Italic Then T = False If .Font.Underline < S.Font.Underline Then T = False End With If T = True Then Total = Total + cell End If Next cell CountFormats = Total End Function If you are only interested in the background color take out the 4 lines for Font. This creates a custom function, which you use by typing a formula such as this in the spreadsheet: =SumFormats(A1:F13,H1) This formula would check the format of H1 and sum all the cells in the range A1:F13 which had the same color format. -- Thanks, Shane Devenshire "lindateammom" wrote: I am wanting to add specifice colored cells only on my worksheet, the colors represent stages that different orders are in and would like to keep a running dollar amount total in each stage - maybe a conditional format? I don't know how to tell excel to add only a certain color of cell - is it possible without buying an additional program? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colored and filtered cells | New Users to Excel | |||
sum colored cells | Excel Discussion (Misc queries) | |||
Adding sums only in certain colored cells | Excel Worksheet Functions | |||
Vlookup using Colored Cells | Excel Discussion (Misc queries) | |||
Select only colored cells | Excel Worksheet Functions |