Conditional Formatting Across Worksheets

[Chris11]

Hi All,

New to this forum but im hoping you can help, as I'm struggling to get this task done at work.

I've got a conditional formatting macro for my worksheet as I have more than 3 conditions.
But now I need to use information from another worksheet (in the same workbook) in the macro.
Basically I dont know how to reference the new worksheet.

This is what I Currently have:



Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("D5:D69")
For Each Cell In MyPlage

If Cell.Value = "8601" Then
Cell.Interior.ColorIndex = 41
End If

If Cell.Value = "8501" Then
Cell.Interior.ColorIndex = 43
End If

If Cell.Value = "4901" Then
Cell.Interior.ColorIndex = 46
End If

If Cell.Value = "4902" Then
Cell.Interior.ColorIndex = 44
End If

If Cell.Value = "3802" Then
Cell.Interior.ColorIndex = 47
End If

If Cell.Value = "3802(D)" Then
Cell.Interior.ColorIndex = 7
End If

If Cell.Value = "8504" Then
Cell.Interior.ColorIndex = 18
End If

If Cell.Value = "Not listed" Then
Cell.Interior.ColorIndex = 15
End If


Next
End Sub



Thanks in advance!

Chris A

 

[RoryA]

Forgot a bit:

Set rngData = Sheet3.Range("A2:A40")
        For Each rngCell In Sheet1.Range("D6:N71").Cells
        Colourcell rngCell, rngData
        Next rngCell

No - changing a colour does not trigger any events you can trap, but you should be able to use the Worksheet_DeActivate event of Sheet3 to run the code to recolour the cells.

 

[Chris11]

Forgot a bit:

Set rngData = Sheet3.Range("A2:A40")
        For Each rngCell In Sheet1.Range("D6:N71").Cells
        Colourcell rngCell, rngData
        Next rngCell

No - changing a colour does not trigger any events you can trap, but you should be able to use the Worksheet_DeActivate event of Sheet3 to run the code to recolour the cells.

Hmm ok.

I think ive got something wrong with this part on Sheet3 code:

Sub Worksheet_Change(ByVal Target As Range)

Debug is not liking it!

 

[RoryA]

Full code please? (that line is fine)

 

[Chris11]

Full code please? (that line is fine)

Sub Worksheet_Change(ByVal Target As Range)
        Set rngData = Sheet3.Range("A2:A40")
        For Each rngCell In Sheet1.Range("D6:N71").Cells
        Colourcell [B]rngCell[/B], rngData
        Next rngCell
End Sub

First line is highlighted yellow. Same compile error as before.
Might be the part i put in bold ?

 

[RoryA]

Need to declare the variables:

Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell as Range
    Dim rngData as range
        Set rngData = Sheet3.Range("A2:A40")
        For Each rngCell In Sheet1.Range("D6:N71").Cells
        Colourcell rngCell, rngData
        Next rngCell
End Sub

 

[Chris11]

YAY!! Finally works how i wanted it to!

Thanks for all your help Rory

...Lets see if my boss cares now...

 

[RoryA]

Meh. Who cares about bosses?