AVP
AVP

Reputation: 1

VBA - Change cell background color

I've been building a team vacation calendar in Excel (Office 365 version) and I'm using VBA for the first time to automate some calculations and styling.

I've been stuck on the following:

I want to create a function that changes the background color of a cell. I have four colors to switch between so I'd rather make four functions, one per color. That function will then be called within different functions when needed. I don't want to use ColorIndex, but rather a custom color (I can use RGB or the Long value), but I can't get the ColorIndex to work either.

My assumption is that the problem lies with the range but at this point, who knows :D.

The long values of each color are stored within a self-made Enum "OwnColorLong".

Here are some of my tries, every time the result in my Excel sheet (when running as a formula) is "#Value!".

'Function SetBackgroundToRed(RangeToChange As Range)
'    Dim ColorIWant As Long
'    ColorIWant = OwnColorLong.Red         
'    RangeToChange.Interior.Color = ColorIWant 
'End Function

'Sub SetColorToRed(RangeToChange As Range)
'    RangeToChange.Select
'    With Selection.Interior
'        .ColorIndex = 3
'    End With
'End Sub
'
Function SetBackgroundToRed(RangeToChange As Range)
   Dim MyRange As Range
   Set MyRange = Worksheets("Vacation Calendar").Range("RangeToChange")
    MyRange.Select
    With Selection.Interior
        .ColorIndex = 3
    End With
End Function

I'm still a bit confused about when to use a sub or a function, or when to best use a class module. All code is now placed within one module, I'll be writing a Main sub linked to a button and putting all the code in there except for the functions themselves. If there are better practices, feel free to let me know.

Upvotes: 0

Views: 6850

Answers (3)

AVP
AVP

Reputation: 1

I got this to work eventually!

Dim rng As String
rng = "A1"
Range(rng).Interior.Color = OwnColorLong.Red

The problem was not knowing how to pass a range as a variable, I had to use String apparently, not Range.

Thanks everyone for the help!

Upvotes: 0

BigBen
BigBen

Reputation: 49998

  • This won't work as a UDF called from a worksheet cell. Except for some edge cases, e.g. this, UDFs called from a cell can't modify other cells on the worksheet.

    Functions can perform a calculation that returns either a value or text to the cell that they are entered in. Any environmental changes should be made through the use of a Visual Basic subroutine.

  • Prefer Sub to Function since this does something and doesn't return anything.
  • "I have four colors to switch between so I'd rather make four functions, one per color" - better to make one function and pass a color parameter.
Private Sub SetColor(ByVal RangeToChange As Range, ByVal Color As Long)
    RangeToChange.Interior.Color = Color
End Sub

Called like

SetColor yourRange, OwnColorLong.Red  

Upvotes: 1

Sam
Sam

Reputation: 5721

Second, Sub or Function? If you need an answer from your method, then Function it is. In your case, you need no answer, so it is Sub.

Sub SetBackgroundToRed(RangeToChange As Range)
    With RangeToChange.Interior
        .ColorIndex = 3
    End With
End Sub

Upvotes: 0

Related Questions