Wondering if it could be done

[Spare_Key]

Good day everyone,

I am a rookie soft and true, been lurking here for a about a week or two. I require some excel assistance with an excel I am working with. I have a great idea but I dont know how to apply it as I fairly fresh to VBcode. I am trying to run a formula where a specified cell containg "s" changes the color of the cell (conditional formating) however I would like the "s" removed from the cell. I would like to keep the cell clean so I can sum the numbers up once the data is entered for the month (Each individual colour to have its own sum). I found a Macro online that removed the letters from the cells but removed the conditional formating since the letter was no longer present.

I belive I am looking for three different macros, or one that would work with all of these rules;
-Cell containing specific letter colors cell, letter does not show up in cell.
-Colored cells do not add up with regular cells.
-Coloured cells sum up by colour.


I appriciate any help anyone could give me. If you require anymore information please ask I will check on a daily basis.


Thank you.

 

[Spare_Key]

Good afternoon,

Yes.
This particula excel is a vacation planner. I am trying to correct some of the issues we had in the past and make this excel more user friendly. In the past users would enter 8 (8hrs vacation) and add a comment "sick" or "no pay leave". The sum function would pick up the numerical values and add everything up which threw off the monthly and yearly data. I thought of an idea where colours would separate the sick time away time and so on from regular vacation time and get added with in their own field. Howevre I am a bit of an excel novice and this project is a bit more complex then i thought it was going to be.

 

[problemss]

If you are able to view this at home please go back and read the links. They have good information that will help teach you instead of being spoon fed.......here comes the spoon.

Open your VBA editor, double click the sheet from the hierarchy on the left and paste this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    
    Dim rng As Range
    Application.EnableEvents = False
    Application.ScreenUpdating = True
    
    'MsgBox "Selection Changed"
    
    If Application.CutCopyMode Then
      'allow copying and pasting on the worksheet
      GoTo errHandler
    End If
    
    Set rng = ActiveSheet.UsedRange
    On Error Resume Next
    For i = 1 To rng.Columns.Count
        For j = 1 To rng.Rows.Count
            Select Case Left(rng.Cells(i, j), 1)
                Case "s"
                    rng.Cells(i, j).Interior.Color = RGB(230, 185, 184)
                    rng.Cells(i, j) = Right(rng.Cells(i, j), 1) 'Assuming no two digit numbers
                Case "f"
                    rng.Cells(i, j).Interior.Color = RGB(182, 221, 232)
                    rng.Cells(i, j) = Right(rng.Cells(i, j), 1) 'Assuming no two digit numbers
                Case "p"
                    rng.Cells(i, j).Interior.Color = RGB(215, 228, 188)
                    rng.Cells(i, j) = Right(rng.Cells(i, j), 1) 'Assuming no two digit numbers
                Case "a"
                    rng.Cells(i, j).Interior.Color = RGB(255, 255, 0)
                    rng.Cells(i, j) = Right(rng.Cells(i, j), 1) 'Assuming no two digit numbers
                Case Else
                    'Do nothing
            End Select
        Next
    Next
    
        
errHandler:
      Application.EnableEvents = True
      Exit Sub
    
End Sub

It searches all cells on the current sheet and will remove the letter while setting the cell color. It will do this every time the selection changes.

Now create a module, Insert > Module from VBA editor. When in the module (should be module1) paste this

Function SumByColor(cellColor As Range, rRange As Range)    
    Dim cSum As Long
    Dim colIndex As Long
    colIndex = cellColor.Interior.Color
    
    For Each c1 In rRange
        If c1.Interior.Color = colIndex Then
            cSum = WorksheetFunction.Sum(c1, cSum)
        End If
    Next c1
    
    SumByColor = cSum
End Function

This is a User Defined Function (UDF) that you can use on your spreadsheet. Now for each cell in their respective total section (i.e. Total S, Total F, etc) just call the UDF function.

=SumByColor(G4,$A4:$F4)

The G4 is the current cell, it will grab the color of the current cell to determine what color you want to sum. The $A4:$F4 is the range you want to sum.

 

[Spare_Key]

Thank you very much for all of your help.
Everything worked great and the functions did exactly what they were supposed to do.
I played a bit with is here and there and gave it a trail run. I ended up running into a major snag that I didnt aticipate till it was mentioned by one of our team members. Some of the users who will utilize this sheet and the work station that they will be using have their macros disabled. This could be a bit of a snag as to how the data will be viewed and entered.

Thank you once agian for all of your help, much appriciated.