Cell Parameters

[HansV]

When you want to assign a range to rng, you use rng as a Range object.
But when you specify a fill color or text color, you don't assign a value to the Range object, but to one of its properties. This property is not an object but a number, so you don't use Set.

[bknight]

Seems to make sense. The full correct code incase anyone follows the initial question.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim TopLeft As Range
    Dim cel As Range
    Dim rng As Range
    Set TopLeft = Range("A2").End(xlDown)
    Set cel = Range("A2").End(xlDown).Offset(0, 3)
    Set rng = Range(TopLeft, TopLeft.Offset(0, 14))
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        If cel.Value Like "*Open" Then
            rng.Interior.Color = 13551615
        ElseIf cel.Value Like "*Close" Then
            rng.Interior.ColorIndex = xlAutomatic
            rng.Font.Color = 0
        Else
        End If
        Application.ScreenUpdating = True
        Application.EnableEvents = True
End Sub