Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM numbers of a certain font color
I am making a sheet of customers that have contracts to build objects. In
column A, there is a list of prices that are paid and not paid. The paid items are formated in red font color. Column B is the amount we have paid them. Is there a way to formate colum B so that when i change a number to red in column A, it will automatically update with the new SUM in colmn B? Hopefully not too confusing. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM numbers of a certain font color
Give this a try. Where there is a named range you want to count the red
fonts is named DataY Sub SumColorCountRed() Dim Red3 As Integer Dim Cell As Range For Each Cell In Range("DataY") If Cell.Font.ColorIndex = 3 Then Red3 = Red3 + Cell.Value End If Next Range("F1").Value = "Red = " & Red3 MsgBox " Red adds to " & Red3, _ vbOKOnly, "CountColor" Range("F1").Value = "" End Sub HTH Regards, Howard "gudencough" wrote in message ... I am making a sheet of customers that have contracts to build objects. In column A, there is a list of prices that are paid and not paid. The paid items are formated in red font color. Column B is the amount we have paid them. Is there a way to formate colum B so that when i change a number to red in column A, it will automatically update with the new SUM in colmn B? Hopefully not too confusing. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM numbers of a certain font color
You can do it with VBA. Put the following code in a module in your
workbook: Function SumColor(RR As Range, _ ColorIndex As Long, _ Optional OfText As Boolean = False) As Double Dim R As Range Dim D As Double For Each R In RR.Cells If OfText = True Then If R.Font.ColorIndex = ColorIndex Then If IsNumeric(R.Value) Then D = D + R.Value End If End If Else If R.Interior.ColorIndex = ColorIndex Then If IsNumeric(R.Value) Then D = D + R.Value End If End If End If Next R SumColor = D End Function Then, you can call it from a worksheet cell with a formula like =SumColor(A1:A100,3,TRUE) The first parameter is the range of cells to test and sum. The second argument is the ColorIndex of the cell that should be summed. In this example, 3 indicates red. The third parameter should be TRUE if you want to test font color or FALSE if you want to test background fill color. See www.cpearson.com/Excel/Colors.aspx for much more information about working with colors in Excel. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 20 May 2010 14:56:01 -0700, gudencough wrote: I am making a sheet of customers that have contracts to build objects. In column A, there is a list of prices that are paid and not paid. The paid items are formated in red font color. Column B is the amount we have paid them. Is there a way to formate colum B so that when i change a number to red in column A, it will automatically update with the new SUM in colmn B? Hopefully not too confusing. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM numbers of a certain font color
Hi Chip,
I find that pretty darn slick. And playing with it, I see you can use a cell reference for the True/False to toggle from font color to cell color, say with a two item drop down list. Goes in my archives. Regards, Howard "gudencough" wrote in message ... I am making a sheet of customers that have contracts to build objects. In column A, there is a list of prices that are paid and not paid. The paid items are formated in red font color. Column B is the amount we have paid them. Is there a way to formate colum B so that when i change a number to red in column A, it will automatically update with the new SUM in colmn B? Hopefully not too confusing. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM numbers of a certain font color
This works great! Thank you.
Now i am wondering if there is a way for column B to automatically update with a new sum when you format another number to red in coumn A? Right now if you format a new number to red, you have to double click in column B and press enter to update it. I'm wanting this for ease, and if another user uses the spreadsheat in the future, there will be no confusion if they forget to update column B. "Chip Pearson" wrote: You can do it with VBA. Put the following code in a module in your workbook: Function SumColor(RR As Range, _ ColorIndex As Long, _ Optional OfText As Boolean = False) As Double Dim R As Range Dim D As Double For Each R In RR.Cells If OfText = True Then If R.Font.ColorIndex = ColorIndex Then If IsNumeric(R.Value) Then D = D + R.Value End If End If Else If R.Interior.ColorIndex = ColorIndex Then If IsNumeric(R.Value) Then D = D + R.Value End If End If End If Next R SumColor = D End Function Then, you can call it from a worksheet cell with a formula like =SumColor(A1:A100,3,TRUE) The first parameter is the range of cells to test and sum. The second argument is the ColorIndex of the cell that should be summed. In this example, 3 indicates red. The third parameter should be TRUE if you want to test font color or FALSE if you want to test background fill color. See www.cpearson.com/Excel/Colors.aspx for much more information about working with colors in Excel. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 20 May 2010 14:56:01 -0700, gudencough wrote: I am making a sheet of customers that have contracts to build objects. In column A, there is a list of prices that are paid and not paid. The paid items are formated in red font color. Column B is the amount we have paid them. Is there a way to formate colum B so that when i change a number to red in column A, it will automatically update with the new SUM in colmn B? Hopefully not too confusing. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Font color for negative numbers | New Users to Excel | |||
Font color for negative numbers | New Users to Excel | |||
Can I total only numbers with a specific font color? | Excel Discussion (Misc queries) | |||
calculate numbers based on font color? | Excel Worksheet Functions | |||
How do I display negative numbers in a differnt color font? | Excel Discussion (Misc queries) |