Highlight in Sheet2 a change in Sheet1

[jcastro]

I need to highlight changes in a workbook but, in addition to the cell that's changed, I need to highlight a cell in another worksheet. For example, Sheet1 cell B4 equals 2 and Sheet2 cell E4 contains =Sheet1!B4. When I change the 2 to a 3, the cell in Sheet1 is highlighted. Sheet2 shows the new value but is not highlighted because the content of the cell, the formula, has not changed. I understand the "why" but I don't know how to make it do what I want.

Track Changes from the menu bar doesn't help and I know how to Conditional Format depending on a cell value but not how to apply it based on a change. So far, I've copied the following code I found here to each sheet (14 sheets total) but it only highlights the cell itself.

<CODE>
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = vbCyan
End Sub
</CODE>

Any insight will be greatly appreciated. Thanks!
JCastro

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = vbCyan
Sheets("Sheet2").Range(Target.Address).Interior.Color = vbCyan
End Sub

 

[jcastro]

I appreciate the help. But the cell in Sheet2 is not the same cell address as in Sheet1. They're both in the same row but not in the same column. For example, I need the change in Sheet1!B4 hightlighted in Sheet2!E4. Thanks!

JCastro

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = vbCyan
Sheets("Sheet2").Range(Target.Address).Offset(, 3).Interior.Color = vbCyan
End Sub

 

[jcastro]

Hooray! That worked like a charm. Thanks, VoG!
JCastro

 

[jcastro]

Thanks again, VoG. I'm not sure if I should continue in this thread or start a new one. The added wrinkle is that I'm changing cells in several columns and the "related" cells in other sheets are not in the same positions.

In other words, changes in Sheet1 column B cells need to be highlighted in Sheet2 column E but changes in Sheet1 column G cells need to be highlighted in Sheet2 column F. And changes in in Sheet1 column H cells need to be highlighted in Sheet3! It would seem that I need code tied to a cell more than a specific sheet.

 

[VoG]

If there aren't zillions of related columns then the following approach should be OK.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = vbCyan
If Target.Column = 2 Then
    Sheets("Sheet2").Range(Target.Address).Offset(, 3).Interior.Color = vbCyan
ElseIf Target.Column = 7 Then
    Sheets("Sheet2").Range(Target.Address).Offset(, -1).Interior.Color = vbCyan
ElseIf Target.Column = 8 Then
    Sheets("Sheet3").Range(Target.Address).Interior.Color = vbCyan
End If
End Sub

 

[jcastro]

I'm going to test this code this afternoon (in Florida). There aren't that many columns so this should work. Thank you!

JCastro

 

[jcastro]

It worked excellent! I tailored the sheet names and columns to each of the 14 sheets in my workbook and learned quite a bit in the process. Thanks again, VoG!