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


"(E-Mail Removed)" 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


"(E-Mail Removed)" 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 <ryguy7...@discussions.microsoft.com>
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
>
>
>
> "michaelsdu...@gmail.com" 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.