Target Value from another sheet.

[erutherford]

I currently have the following code in "worksheet"/SelectionChange and it works fine. I am sure there is a simpler to way to write the code, but right now it easier to find code and modify it.

Private Sub Worksheet_Change(ByVal Target As Range)
       If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub 'check for range
    If Target.Value = "W" Then 'check if "W"
        Target.Offset(0, 6).Interior.ColorIndex = 1 ' "1"=Black
        Target.Offset(0, 7).Interior.ColorIndex = 1
        Target.Offset(0, 6).Font.ColorIndex = 3 ' "3"=Red
        Target.Offset(0, 7).Font.ColorIndex = 3 ' "3"=Red
    ElseIf Target.Value = "S" Then 'check if "S"
        Target.Offset(0, 6).Interior.ColorIndex = 1 ' "1"=Black
        Target.Offset(0, 7).Interior.ColorIndex = 1
        Target.Offset(0, 6).Font.ColorIndex = 3 ' "3"=Red
        Target.Offset(0, 7).Font.ColorIndex = 3 ' "3"=Red
    Else
        Target.Offset(0, 6).Interior.ColorIndex = 27
        Target.Offset(0, 7).Interior.ColorIndex = 27
        Target.Offset(0, 6).Font.ColorIndex = 1
        Target.Offset(0, 7).Font.ColorIndex = 1
    End If
End Sub

In column I there is code

=Entries!I1

Target.value doesn't like reading that code. If I delete the code and just enter the letter it works fine. I need it to run off the formula.

Thanks in advance

 

[Fluff]

Change events don't work if the cell value is a formula.
Is col I on the Entries sheet manually entered or another formula?

 

[erutherford]

Manually entered in on "Entries" sheet. I was thinking that might be the answer is to point the code there, just didn't quite know how to do it.

 

[Fluff]

I've done the first section, to show how it's done
Put this on the Entries sheet

Private Sub Worksheet_Change(ByVal Target As Range)
   If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub 'check for range
   [COLOR=#0000ff]With Sheets("[/COLOR][COLOR=#ff0000]Project[/COLOR][COLOR=#0000ff]")[/COLOR]
      If Target.Value = "W" Then 'check if "W"
          [COLOR=#0000ff].Range(Target.Offset(0, 6).Address).Resize(, 2).Interior.ColorIndex = 1 ' "1"=Black
          .Range(Target.Offset(0, 6).Address).Resize(, 2).Font.ColorIndex = 3 ' "3"=Red[/COLOR]
      ElseIf Target.Value = "S" Then 'check if "S"
          Target.Offset(0, 6).Interior.ColorIndex = 1 ' "1"=Black
          Target.Offset(0, 7).Interior.ColorIndex = 1
          Target.Offset(0, 6).Font.ColorIndex = 3 ' "3"=Red
          Target.Offset(0, 7).Font.ColorIndex = 3 ' "3"=Red
      Else
          Target.Offset(0, 6).Interior.ColorIndex = 27
          Target.Offset(0, 7).Interior.ColorIndex = 27
          Target.Offset(0, 6).Font.ColorIndex = 1
          Target.Offset(0, 7).Font.ColorIndex = 1
      End If
   End With
End Sub

Change sheet name in red to suit

 

[erutherford]

Just to be clear we are on the same page

The "entries" sheet in col I are manually entered ("F"or"W","S").
On sheet "Scores" in Col I, cells get there values from the entries sheet via "=entries!I1, I2" etc.
Columns "6" and "7" are manipulated based on the value in sheet "Scores" in Col I.

But that won't work as you stated above.

I pasted the new code in the "entries" worksheet and it executes on that page, but not in the "scores" sheets

In the above modified code, where is the sheet "scores" referenced? I probably didn't explain it clear.

 

[Fluff]

You need to change the sheet name I highlighted in red to "Scores"
At the moment it will only work on the "W" values, you need to modify the other sections, in the same manner.

 

[erutherford]

Ok it works now and I'll modify the rest no problem.

With Sheets("Scores") is pointing to our destination. What is ".address.Resize doing?

I like to at least understand a little about what is happening.

Thanks for helping out of course Fluff!

 

[Fluff]

This

[COLOR=#0000ff]Target.Offset(0, 6).Address[/COLOR]

returns the address of the the cell 6 columns to the right of the target. So if the target cell was I5 you will get $O$6 which then means that this

.Range(Target.Offset(0, 6).Address).Resize(, 2)

Is Range($O$5) and the Resize then changes the size of the range to make it 2 cells wide ie O5:P5

 

[erutherford]

Thanks again

Happy Holidays!

 

[Fluff]

You're welcome