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.
Cell Parameters
-
- Administrator
- Posts: 78536
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Parameters
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1391
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
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