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.

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---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


" 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.

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.

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.

On Jul 22, 2:41*pm, ryguy7272
wrote:
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---

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


On Jul 22, 3:38*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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.