I have a macro that shades cells a certain color based on a specific value. The macro works fine but when I try to link that cell with a cell on another sheet only the value comes across not the cell color.
What am I missing? Is it the macro or is there something not set correctly on the worksheet?
Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)
Dim Position As String
Dim vColor As Integer
Dim cell As Range
For Each cell In target
Model = cell.Value
vColor = 0 'default is no color
Select Case Model
Case "120D"
vColor = 45
Case "160D"
vColor = 43
Case "200DL"
vColor = 42
Case "240DL"
vColor = 40
Case "120Z"
vColor = 38
Case "160Z"
vColor = 48
Case "200Z"
vColor = 47
Case "240Z"
vColor = 50
Case "270Z"
vColor = 39
End Select
Application.EnableEvents = False
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True
Next cell
End Sub
Thanks in advance!
What am I missing? Is it the macro or is there something not set correctly on the worksheet?
Option Explicit
Private Sub Worksheet_Change(ByVal target As Range)
Dim Position As String
Dim vColor As Integer
Dim cell As Range
For Each cell In target
Model = cell.Value
vColor = 0 'default is no color
Select Case Model
Case "120D"
vColor = 45
Case "160D"
vColor = 43
Case "200DL"
vColor = 42
Case "240DL"
vColor = 40
Case "120Z"
vColor = 38
Case "160Z"
vColor = 48
Case "200Z"
vColor = 47
Case "240Z"
vColor = 50
Case "270Z"
vColor = 39
End Select
Application.EnableEvents = False
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True
Next cell
End Sub
Thanks in advance!