change current cell colour based on the value of adjacent cell on other worksheet

[Rits]

I am currently working on one complicated excel spreadsheet. I wish to
change current cell colour based on the value of adjacent cell on other
worksheet. In simple words I have 2 worksheets. On worksheet 1 to
monitor the project status I am using conditional formatting. for
example if value of cell A1 is equal to Completed then cell colour
changes to GREEN if it's Hold then RED and if Progressing then
YELLOW. This is standard !!!

Now on 2nd worksheet I wish to monitor the value of the cell A1 on
worksheet 1 and if value changes from blank to Completed , Hold or
Progressing then I wish to change the colour of the cell B1 on
worksheet 2.

Is it possible to achieve this in Microsoft Excel? I will appreciate
if someone tell me how to do this?

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Worksheets("Sheet2")
Select Case Target.Value
Case "Completed":
.Range(Target.Offset(0,1).Address(False,
False)).Interior.ColorIndex = 3
Case "Hold":
.Range(Target.Offset(0,1).Address(False,
False)).Interior.ColorIndex = 5
Case "Pending":
.Range(Target.Offset(0,1).Address(False,
False)).Interior.ColorIndex = 6
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Conditional formatting will not operate across worksheets. The usual
approach is to use a link..If you want to monitor celll A1 on Sheet1, then on
Sheet2 in any cell, say Z100, put a link to the Sheet1 cell and use Z100 for
the conditional formatting reference.