You can not select on a non active sheet. That being said you have no need to
select. Target is the range so just use that...

Private Sub Worksheet_Change(ByVal RoadShow As Excel.Range)
Dim Source As Range
Dim Target As Range
Dim CellVal As Integer
Dim CellTar As Integer
Dim LastLoc As Range

If RoadShow.Cells.Count 1 Then Exit
If RoadShow = "" Or Not IsNumeric(RoadShow) Then Exit Sub
CellVal = RoadShow
LastLoc = RoadShow.Address

Set Target = Worksheets("Costco").Range(LastLoc).offset(5,0) 'the change
Set Source = Range("C4:AB45")

If Not Intersect(RoadShow, Source) Is Nothing Then

CelTar = Target.Interior.ColorIndex
If CellVal = 1 Then
Select Case CellTar
Case 27
With Target.Interior
.ColorIndex = 55
.Pattern = xlPatternVertical
.PatternColorIndex = 27
End With
Case 42
With Target.Interior
.ColorIndex = 55
.Pattern = xlPatternVertical
.PatternColorIndex = 42
End With
Case ""
Target.Interior.ColorIndex = 55
End Select
End If
End If
End Sub


--
HTH...

Jim Thomlinson


" wrote:

I am trying to use conditional formating from input from a second
sheet.

The ranges are relative: offset by +5 vertically. I have tried the
code a few ways, but I cannot figure out how to translate the range
from one sheet to another.

Here is what I have:


Private Sub Worksheet_Change(ByVal RoadShow As Excel.Range)
Dim Source As Range
Dim Target As Range
Dim CellVal As Integer
Dim CellTar As Integer
Dim LastLoc As Range

If RoadShow.Cells.Count 1 Then Exit
If RoadShow = "" Or Not IsNumeric(RoadShow) Then Exit Sub
CellVal = RoadShow
LastLoc = RoadShow.Address

Set Target = Worksheets("Costco").Range(LastLoc)
Set Source = Range("C4:AB45")

If Not Intersect(RoadShow, Source) Is Nothing Then

Target.Offset(5, 0).Select
CelTar = Target.Interior.ColorIndex
If CellVal = 1 Then
Select Case CellTar
Case 27
With Target.Interior
.ColorIndex = 55
.Pattern = xlPatternVertical
.PatternColorIndex = 27
End With
Case 42
With Target.Interior
.ColorIndex = 55
.Pattern = xlPatternVertical
.PatternColorIndex = 42
End With
Case ""
Target.Interior.ColorIndex = 55
End Select
End If
End If
End Sub



Thank you for the help.