Need help with row calcs

[carrion]

Hello,

I'm having a great deal of trouble for such a lame reason. Here's my problem.

I'm redoing a schedule for my work. Each row contains color coded cells to represent different shifts and different locations of the shifts. In order to tell the cells to be a certain color, I wrote a macro to automatically format cells instead of having to do them manually (which what was previously done).

Here is the code for that macro if it helps at all:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Range("B2:AE12")   'Active Range to apply macro to.  Range needs to be changed with additional rows/colums
        With Target
            If .Cells.Count >= 1 Then
                Select Case .Value
                    Case "l"
                        .Borders.Color = RGB(0, 0, 0)
                        .Borders.Weight = xlMedium
                        .Interior.ColorIndex = 8
                    Case "k"
                        .Borders.Color = RGB(0, 0, 0)
                        .Borders.Weight = xlMedium
                        .Interior.ColorIndex = 6
                    Case "m"
                        .Borders.Color = RGB(0, 0, 0)
                        .Borders.Weight = xlMedium
                        .Interior.ColorIndex = 10
                    Case "p"
                        .Borders.Color = RGB(0, 0, 0)
                        .Borders.Weight = xlMedium
                        .Interior.ColorIndex = 46
                    Case "r"
                        .Borders.Color = RGB(0, 0, 0)
                        .Borders.Weight = xlMedium
                        .Interior.ColorIndex = 5
                    Case "o"
                        .Borders.Color = RGB(0, 0, 0)
                        .Borders.Weight = xlMedium
                        .Interior.ColorIndex = 7
                    Case "c"
                        .Borders.Color = RGB(0, 0, 0)
                        .Borders.Weight = xlMedium
                        .Interior.ColorIndex = 16
                    Case Else
                        Selection.ClearFormats
                End Select
            End If
        End With
    End With
End Sub

Now I need to add the total hours for the day at the end of the row. I want to be able to make a macro that will calculate the total hours at the end of each row based on whether there is one of the "case" letters from above in the cell. I don't want to include cells with a "c" counted towards the total hours because those are hours that us student workers have claimed to have classes on (...claimed lol) so working would be impossible.

I hope I got what I was thinking across and hopefully someone can have to answer I'm looking for. Let me know if I left anything unexplained. Thanks for any help you can give me.

 

[Damon Ostrander]

Hi carrion,

This is a bit confusing. First, you mention a range to apply the macro to as

With Range("B2:AE12")

but this statement is totally inactive as it is because it is immediately overridden by the next With Target statement. If you intend the routine to ignore Targets (changes) outside this range the statement should be

If Intersect(Target,Range("B2:AE12")) Is Nothing Then Exit Sub

and of course the corresponding End With should be deleted.

Regarding adding the total hours at the end of the row, it is not clear whether you want to total columns containing hours in this row and put the result at the end of the row, and where the end of the row is. Is it a particular column, or just the first empty cell in the row? Also, are there other columns other than the one the user is entering a letter (such as "c") into that do not contain hours or that should not be included in the total?

Damon