| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
color coding
Hello, I have a list of charges in a month to month sheet. Whenever a charge needs to be made, I hightligh the cell with light blue so accounting knows that the project needs to be invoiced. What I would like to do is have the amount selected with the color be deducated from the total still to be invoiced. I have created a funtion by pasting the recommended one (please see below) by pasting it a the module I think. The formula recommended was CountColor("--ColorIndex(h2:t35)=41)"). However, I still get the #name? error. What am I doing wrong? I'm also attaching the sheet I'm trying to create. Thanks for your help ! Function CountColor(myColorName As String, myRange As Range) As Integer Dim myColorIndex As Integer Select Case myColorName Case "Black" myColorIndex = 1 Case "Dark Red" myColorIndex = 9 Case "Red" myColorIndex = 3 Case "Pink" myColorIndex = 7 Case "Rose" myColorIndex = 38 Case "Brown" myColorIndex = 53 Case "Orange" myColorIndex = 46 Case "Light Orange" myColorIndex = 45 Case "Gold" myColorIndex = 44 Case "Tan" myColorIndex = 40 Case "Olive Green" myColorIndex = 52 Case "Dark Yellow" myColorIndex = 12 Case "Lime" myColorIndex = 43 Case "Yellow" myColorIndex = 6 Case "Light Yellow" myColorIndex = 36 Case "Dark Green" myColorIndex = 51 Case "Green" myColorIndex = 10 Case "Sea Green" myColorIndex = 50 Case "Bright Green" myColorIndex = 4 Case "Light Green" myColorIndex = 35 Case "Dark Teal" myColorIndex = 49 Case "Teal" myColorIndex = 14 Case "Aqua" myColorIndex = 42 Case "Turquiose" myColorIndex = 8 Case "Light Turquoise" myColorIndex = 34 Case "Dark Blue" myColorIndex = 11 Case "Blue" myColorIndex = 5 Case "Light Blue" myColorIndex = 41 Case "Sky Blue" myColorIndex = 33 Case "Pale Blue" myColorIndex = 37 Case "Indigo" myColorIndex = 55 Case "Blue-Gray" myColorIndex = 47 Case "Violet" myColorIndex = 13 Case "Plum" myColorIndex = 54 Case "Lavender" myColorIndex = 39 Case "Gray-80%" myColorIndex = 56 Case "Gray-50%" myColorIndex = 16 Case "Gray-40%" myColorIndex = 48 Case "Gray-25%" myColorIndex = 15 Case "White" myColorIndex = 2 Case Else myColorIndex = -4142 End Select For Each mycell In myRange If mycell.Interior.ColorIndex = myColorIndex Then CountColor = CountColor + 1 Next mycell End Function -- Ardilla ------------------------------------------------------------------------ Ardilla's Profile: http://www.excelforum.com/member.php...o&userid=30328 View this thread: http://www.excelforum.com/showthread...hreadid=500366 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
color coding
I think it should be
=CountColor("Light Blue",C1:C7) -- HTH RP (remove nothere from the email address if mailing direct) "Ardilla" wrote in message ... Hello, I have a list of charges in a month to month sheet. Whenever a charge needs to be made, I hightligh the cell with light blue so accounting knows that the project needs to be invoiced. What I would like to do is have the amount selected with the color be deducated from the total still to be invoiced. I have created a funtion by pasting the recommended one (please see below) by pasting it a the module I think. The formula recommended was CountColor("--ColorIndex(h2:t35)=41)"). However, I still get the #name? error. What am I doing wrong? I'm also attaching the sheet I'm trying to create. Thanks for your help ! Function CountColor(myColorName As String, myRange As Range) As Integer Dim myColorIndex As Integer Select Case myColorName Case "Black" myColorIndex = 1 Case "Dark Red" myColorIndex = 9 Case "Red" myColorIndex = 3 Case "Pink" myColorIndex = 7 Case "Rose" myColorIndex = 38 Case "Brown" myColorIndex = 53 Case "Orange" myColorIndex = 46 Case "Light Orange" myColorIndex = 45 Case "Gold" myColorIndex = 44 Case "Tan" myColorIndex = 40 Case "Olive Green" myColorIndex = 52 Case "Dark Yellow" myColorIndex = 12 Case "Lime" myColorIndex = 43 Case "Yellow" myColorIndex = 6 Case "Light Yellow" myColorIndex = 36 Case "Dark Green" myColorIndex = 51 Case "Green" myColorIndex = 10 Case "Sea Green" myColorIndex = 50 Case "Bright Green" myColorIndex = 4 Case "Light Green" myColorIndex = 35 Case "Dark Teal" myColorIndex = 49 Case "Teal" myColorIndex = 14 Case "Aqua" myColorIndex = 42 Case "Turquiose" myColorIndex = 8 Case "Light Turquoise" myColorIndex = 34 Case "Dark Blue" myColorIndex = 11 Case "Blue" myColorIndex = 5 Case "Light Blue" myColorIndex = 41 Case "Sky Blue" myColorIndex = 33 Case "Pale Blue" myColorIndex = 37 Case "Indigo" myColorIndex = 55 Case "Blue-Gray" myColorIndex = 47 Case "Violet" myColorIndex = 13 Case "Plum" myColorIndex = 54 Case "Lavender" myColorIndex = 39 Case "Gray-80%" myColorIndex = 56 Case "Gray-50%" myColorIndex = 16 Case "Gray-40%" myColorIndex = 48 Case "Gray-25%" myColorIndex = 15 Case "White" myColorIndex = 2 Case Else myColorIndex = -4142 End Select For Each mycell In myRange If mycell.Interior.ColorIndex = myColorIndex Then CountColor = CountColor + 1 Next mycell End Function -- Ardilla ------------------------------------------------------------------------ Ardilla's Profile: http://www.excelforum.com/member.php...o&userid=30328 View this thread: http://www.excelforum.com/showthread...hreadid=500366 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
