macros code & color based functions help

[Kim via OfficeKB.com]

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

 

[Tom Ogilvy]

With only your workbook open, go to the VBE (alt+F11) and go to the menu.
Do Insert module.

Paste the code in the resulting module (I suspect you have used a sheet
module - remove it from there.)

No lines should show in Red.

The 3 means Red - count colors that are red. You can see the colors by
running a macro like this (have a blank worksheet active)

Sub ShowColorIndexes()
Dim i as Long
for i = 1 to 56
cells(i,1).Value = i
cells(i,2).Interior.ColorIndex = i
Next
End Sub

Place the above in a general module (same as you have). The go to
Tools=>Macro=>Macros, select it (ShowColorIndex) and click Run.


The True/False as the last argument means - if False, look at the cell's
interior color, and if it is true look at the cells font color.

However, you have modified Chips code so it only looks at the interior color
and it only looks at colorindex 39.

since you have not modified the declaration you need to at least do

=SumbyColor(A1:A10,39)

Mail the worksheet to (e-mail address removed)

 

[Kim via OfficeKB.com]

Hi Tom,

I just emailed you an example of the spreadsheet.

Thanks,
kim