Help with Coding about linked worksheet

[saud]

I have a worksheet contains data, this data if for reasons I ente
number ( 1 ) in a specific cells it will give the symbol ( *1 ) and th
cell will turn to red color and if I enter the number ( 2 ) it wil
give ( *2 ) and the cell will turn to blue color and so on for a tota
of four different symbols and colors,
And because of the limitation of the conditional formatting to onl
three I’m using a code.

Thanks, with the help of some guys in this site I got this cod
working

Private Sub Worksheet_Change(ByVal Target As Range)
Set cRange = Intersect(Range("y11:y35"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
On Error GoTo fixit
Application.EnableEvents = False
Select Case Target
Case 1
Target.Interior.ColorIndex = 3
Case 2
Target.Interior.ColorIndex = 28
Case 3
Target.Interior.ColorIndex = 27
Case 4
Target.Interior.ColorIndex = 41
End Select
Target = "*" & Target
fixit:
Application.EnableEvents = True
End Sub

My problem is that the information in the first worksheet is linked t
a second worksheet in the same workbook and when I enter any of th
four numbers in the first worksheet its working fine but in the secon
worksheet it will show only the symbol but not the color.


I will be grateful if any one will help me in adding to this code o
getting a new code to change the color in the second worksheet if
change the first one.

Thank, sau

 

[Bernie Deitrick]

Saud,

You basically need to use the same sort of routine, but check all the
cells that have links on the second worksheet. Use the Workbook's
calculate event to force the check: see example code below, which will
work on links in cells A1:B2.

Also, note that your line:
Set cRange = Intersect(Range("y11:y35"), Range(Target(1).Address))
could be better written:
Set cRange = Intersect(Range("y11:y35"), Target(1))

Also, though .Value is the default property of any range object, it is
good practice to use
Select Case Target.Value
rather than
Select Case Target

and

Target.Value = "*" & Target.Value
instead of
Target = "*" & Target

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
Dim myCell As Range

On Error GoTo fixit
Application.EnableEvents = False
For Each myCell In Range("A1:B2")
Select Case CInt(Right(myCell.Value, 1))
Case 1
myCell.Interior.ColorIndex = 3
Case 2
myCell.Interior.ColorIndex = 28
Case 3
myCell.Interior.ColorIndex = 27
Case 4
myCell.Interior.ColorIndex = 41
End Select
Next myCell
fixit:
Application.EnableEvents = True
End Sub