Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude gray figures from sum
Using Excel 2003. In my budget, I use to mark not-yet-payed costs in
gray color. I use the gray color "Gray -40 %" from Font Color on the toolbar. I'm trying to make a method that will sum up the costs, excluding the gray ones. Here's my try: ' Get current column iColumn = Application.ActiveCell.Column ' Loop through range For Each c In Range(Cells(22, iColumn), Cells(30, iColumn)) If ... < ... Then nSum = nSum + c.Value End If Next c ' Print sum Cells(31, iColumn) = nSum The problem is that I don't know what to write after If. What property identifies the font color, and how do I identify the exact gray color from Font Color on the toolbar? Gustaf |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude gray figures from sum
Hi,
Have a look at Bob's excellent solution ... http://www.xldynamic.com/source/xld.ColourCounter.html HTH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude gray figures from sum
Hi,
this function will sum by color. To enable summing by different colours you call the function with =Colorfunction($A$1) where A1 is the same color as the cells you want to sum. Function ColorFunction(MyRange As Range) iColumn = Application.ActiveCell.Column Set ColRange = Range(Cells(22, iColumn), Cells(30, iColumn)) Dim c As Range Dim IntColour As Long Dim Output IntColour = MyRange.Interior.ColorIndex For Each c In ColRange If c.Interior.ColorIndex = IntColour Then Output = Output + c.Value End If Next ColorFunction = Output End Function Mike "Gustaf" wrote: Using Excel 2003. In my budget, I use to mark not-yet-payed costs in gray color. I use the gray color "Gray -40 %" from Font Color on the toolbar. I'm trying to make a method that will sum up the costs, excluding the gray ones. Here's my try: ' Get current column iColumn = Application.ActiveCell.Column ' Loop through range For Each c In Range(Cells(22, iColumn), Cells(30, iColumn)) If ... < ... Then nSum = nSum + c.Value End If Next c ' Print sum Cells(31, iColumn) = nSum The problem is that I don't know what to write after If. What property identifies the font color, and how do I identify the exact gray color from Font Color on the toolbar? Gustaf |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude gray figures from sum
.... forgot to tell you the color index for Grey 40% is : 48
HTH |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude gray figures from sum
On Jan 5, 7:57 pm, Gustaf wrote:
Using Excel 2003. In my budget, I use to mark not-yet-payed costs in gray color. I use the gray color "Gray -40 %" from Font Color on the toolbar. I'm trying to make a method that will sum up the costs, excluding the gray ones. Here's my try: ' Get current column iColumn = Application.ActiveCell.Column ' Loop through range For Each c In Range(Cells(22, iColumn), Cells(30, iColumn)) If ... < ... Then nSum = nSum + c.Value End If Next c ' Print sum Cells(31, iColumn) = nSum The problem is that I don't know what to write after If. What property identifies the font color, and how do I identify the exact gray color from Font Color on the toolbar? Gustaf If c.Font.ColorIndex<48 Then When I'm not too sure about a particular property or method I sometimes just record a macro then check out the recorded code. After changing the font color of the selected cell to 40% gray this code was recorded.. With Selection.Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 48 End With hence c.Font.ColorIndex<48 Ken Johnson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude gray figures from sum
I misread your post, you want to exlude a particular color so use this line
If c.Interior.ColorIndex < IntColour Then Mike "Mike H" wrote: Hi, this function will sum by color. To enable summing by different colours you call the function with =Colorfunction($A$1) where A1 is the same color as the cells you want to sum. Function ColorFunction(MyRange As Range) iColumn = Application.ActiveCell.Column Set ColRange = Range(Cells(22, iColumn), Cells(30, iColumn)) Dim c As Range Dim IntColour As Long Dim Output IntColour = MyRange.Interior.ColorIndex For Each c In ColRange If c.Interior.ColorIndex = IntColour Then Output = Output + c.Value End If Next ColorFunction = Output End Function Mike "Gustaf" wrote: Using Excel 2003. In my budget, I use to mark not-yet-payed costs in gray color. I use the gray color "Gray -40 %" from Font Color on the toolbar. I'm trying to make a method that will sum up the costs, excluding the gray ones. Here's my try: ' Get current column iColumn = Application.ActiveCell.Column ' Loop through range For Each c In Range(Cells(22, iColumn), Cells(30, iColumn)) If ... < ... Then nSum = nSum + c.Value End If Next c ' Print sum Cells(31, iColumn) = nSum The problem is that I don't know what to write after If. What property identifies the font color, and how do I identify the exact gray color from Font Color on the toolbar? Gustaf |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude gray figures from sum
Many thanks to all of you helping me with this. Also great advice about
recording a macro to see the properties involved! Gustaf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add up column of figures to exclude hidden decimal amounts | Excel Worksheet Functions | |||
add figures to existing figures in excel | Excel Worksheet Functions | |||
GRAY SCREEN | Excel Discussion (Misc queries) | |||
Shades of Gray | Excel Worksheet Functions | |||
Changing positive figures to minus figures | Excel Worksheet Functions |