Cell Parameters

User avatar
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cell Parameters

Post by 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.

Posts: 767
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by 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
        End If
        Application.ScreenUpdating = True
        Application.EnableEvents = True
End Sub