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

 

[Chris11]

Ok, Last question, I promise.


How do I get it to update Sheet1 if I change something in Sheet3 (for example - one of the Cell Colours).

At the moment I have to go the Function box, select the text and hit enter for it to change.

 

[RoryA]

Changing cell colours does not trigger any events, so you would need to run a macro really.

 

[Chris11]

****.

Time to try and do that then.

 

[Chris11]

****.

Time to try and do that then.

I've had a look around this forum and some other Macro help sites, Cannot work out how to get an event to trigger the Worksheet macro automatically when the cells in sheet3 are changed!

Tried using a Worksheet_activate() macro and nothing changed.

Any ideas?

 

[RoryA]

Move the ColourCell routine to a normal module and make it Public, rather than private. Then in your sheet3 event, you need something like:

For each rngCell in Sheet1.Range("D6:N71").Cells
   ColourCell rngCell
Next rngCell

 

[Chris11]

Like this in the Sheet3 code?:

Sub Worksheet_Change(ByVal Target As Range)
        For Each rngCell In Sheet1.Range("D6:N71").Cells
        ColourCell rngCell
        Next rngCell
        End If
End Sub

Doesn't seem to make a difference so im guessing not.

 

[RoryA]

That should work if you alter a value in the sheet but, as I said, not if you simply alter a colour.

 

[Chris11]

That should work if you alter a value in the sheet but, as I said, not if you simply alter a colour.

Yah got that, but It sounded like you said I could run a macro so that when the colour is changed is, the event is triggered and the cells change automatically?

Also im getting a Compile Error - ByRef argument type mismatch on the code above.

Chris

 

[RoryA]

Yes, but you have to run it. That's what I meant - it's not automatic.

What's the actual code you have - what you posted wouldn't even compile as the End If doesn't belong there.

 

[Chris11]

Yes, but you have to run it. That's what I meant - it's not automatic.

What's the actual code you have - what you posted wouldn't even compile as the End If doesn't belong there.

Oh so there is no way of making it trigger on Sheet1 when i change a colour in Sheet3?

Sheet3 code:

Sub Worksheet_Change(ByVal Target As Range)
        For Each rngCell In Sheet1.Range("D6:N71").Cells
        Colourcell rngCell
        Next rngCell
End Sub

Sheet1 code:

Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rngData As Excel.Range
        Dim rngCell As Excel.Range
        Dim rngMonitor As Excel.Range
 
        Set rngMonitor = Range("D6:N71")
        ' test if changed cells were in range of interest
        If Not Intersect(Target, rngMonitor) Is Nothing Then
        Set rngData = Sheet3.Range("A2:A40")
        For Each rngCell In Intersect(Target, rngMonitor)
         Colourcell rngCell, rngData
        Next rngCell
        End If
    End Sub

Module1 Code:

Sub Colourcell(rngCheck As Excel.Range, rngColours As Excel.Range)
   Dim varmatch
   varmatch = Application.Match(rngCheck.Value, rngColours, 0)
   If Not IsError(varmatch) Then
      rngCheck.Interior.ColorIndex = rngColours.Cells(varmatch).Interior.ColorIndex
   Else
      rngCheck.Interior.ColorIndex = xlColorIndexNone
   End If
End Sub