color coding

[Ardilla]

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

[Bob Phillips]

I think it should be

=CountColor("Light Blue",C1:C7)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ardilla" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>