Chaning Cell Colors within a Function No Conditional Format

[nathansizemore]

Hello,
I am trying to make a scheduling spreadsheet that can give visuals and allow for a lot of variable change. I am needing some input on how to change the color of a cell. I don't want to use conditional formatting, because every time I add a new project, I will have to add the formatting options. I would like to be able to have the User Defined function that I plug in the cell control its color as well as give the cell a value.
Screen shot of what I have going on -

The cells with the red border are the adjustable values. When one of those changes, the hours left in that section will change accordingly. Now, I want to have that cell colored as long as there are values greater than zero in the cell, and the color should be identical to the main section it is. Fabrication is blue, passivation is grey, etc...
This is the code that I have tried, but I obviously get an error -

Public Function weld_remaining(previous_hours As Integer, manpower As Integer, scheduled_days As Integer, work_hours As Integer, productivity As Double, the_day As String) As Integer
    ' Calculate the hours remaining
    Dim x As Double
    
    ' Is this a working day?
    Select Case the_day
        Case "Mon"
            x = (previous_hours - ((manpower * work_hours) * productivity))
        Case "Tue"
            x = (previous_hours - ((manpower * work_hours) * productivity))
        Case "Wed"
            x = (previous_hours - ((manpower * work_hours) * productivity))
        Case "Thu"
            x = (previous_hours - ((manpower * work_hours) * productivity))
        Case "Fri"
            ' Figure out if Fridays are scheduled
            If scheduled_days = 5 Then
                x = (previous_hours - ((manpower * work_hours) * productivity))
            Else
                x = previous_hours
            End If
        Case "Sat"
            ' Figure out if Saturdays are scheduled
            If scheduled_days = 6 Then
                x = (previous_hours - ((manpower * work_hours) * productivity))
            Else
                x = previous_hours
            End If
        Case "Sun"
            ' Figure out if Sundays are scheduled
            If scheduled_days = 7 Then
                x = (previous_hours - ((manpower * work_hours) * productivity))
            Else
                x = previous_hours
            End If
    End Select
    
    If x > 0 Then
        weld_remaining = x
        'With Selection.Interior
            '.Pattern = xlSolid
            '.PatternColorIndex = xlAutomatic
            '.ThemeColor = xlThemeColorAccent5
            '.TintAndShade = 0.399975585192419
            '.PatternTintAndShade = 0
        'End With
    Else
        weld_remaining = 0
    End If
End Function

Is there anything I can do at the end of this function to turn the cell the appropriate color?

Thanks in advance for any help!

 

[nathansizemore]

Bump. I can't believe this forum is this crazy active. All the way into page 5 within 20 hours...

 

[IndyBob]

Bump. I can't believe this forum is this crazy active. All the way into page 5 within 20 hours...

Been there. But, when you bump it also makes the "show threads with zero replies" not work, so it's a tradeoff.

I have't ever messed with the xlThemeColorAccent stuff as I have never cared for how Excel makes sheets look. Perhaps you are making more of this than it has to be. Could you just use a simpler piece of code such as:

ActiveSheetCell.Interior.ColorIndex = 34

which would make the background light blue (in this case). A simple Google search will give you all of the standard color numbers and then there's always the palette option to get an color. Borders can be done similarly. Also, a solid pattern is normally the default, so there isn't a need to specify, though, as I noted, I don't mess with the Excel Themes, so that may not be the case with your spreadsheet.