Hi there,

I'm working on an attendance spreadsheet and need some help since this is
my first time working with macros.

1. I'd like to tally up the sum of certain colored cells. I found this
link: http://cpearson.com/excel/colors.htm whicch leads me to believe that
this sort of function is possible. I tried copying and pasting the code
into a module and it won't run. Here is the code I entered into the module:

Function SumByColorV(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double

Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Interior.ColorIndex = 39)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
End Function

Did I make a mistake somewhere? Also, the webpage says to call this
function =sumbycolorv (A1:A10, 3, False) in a worksheet cell. What does
the 3 and false represent?





2. I also want to change the color of a cell based on the first letter.
This webpage shows the forumula:

www.mvps.org/dmcritchie/excel/event.htm#case

And here is the code I entered into a module:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vLetter As String
Dim vColor As Integer
Dim cRange As Range
Dim cell As Range

Set cRange = Intersect(Range("B3:X28"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub

For Each cell In Target
vLetter = UCase(Left(cell.Value & " ", 1))
vColor = 0
Select Case vLetter
Case "V"
vColor = 39
Case "S"
vColor = 38
Case "E"
vColor = 3
Case "P"
vColor = 46
Case "T"
vColor = 34
Case "F"
vColor = 37
Case "W"
vColor = 50
Case "R"
vColor = 29
Case "L"
vColor = 41
End Select
Application.EnableEvents = False
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True
Next cell
End Sub

Again, when I go to run the macro, it doesn't show up. Where is there a
mistake in the code?

If it helps, I can email you the worksheet so you can take a look yourself.

I really appreciate any help/advice.

Sincerely,
kim

--
Message posted via http://www.officekb.com