Conditional Formatting with reference to another worksheet VBA

[michaelsdumas]

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.

 

[ryguy7272]

I don't think you can apply CF to sheets other than the activesheet, but see
this link for a possible workaround:
http://www.cpearson.com/excel/cformatting.htm

Good luck,
Ryan---

 

[Jim Thomlinson]

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

 

[michaelsdumas]

Thanks Ryan, I am honestly looking for an event based on a relative
cell in another worksheet. This does not have to be specific.

Thanks.

 

[michaelsdumas]

Thank you. There is something odd going on now.

I am getting a do sub error on the line: If RoadShow.Cells.Count > 1
Then Exit