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

 

[villy]

set ws1 = Sheets("sheet1").Range("A1:D20")
set ws2 = Sheets("sheet2").Range("A1:D20")
set wsData = Sheets("Data").Range("DataFile") 'DataFile is a defined Name Range

 

[Chris11]

set ws1 = Sheets("sheet1").Range("A1:D20")
set ws2 = Sheets("sheet2").Range("A1:D20")
set wsData = Sheets("Data").Range("DataFile") 'DataFile is a defined Name Range

Cheers man, Although im unsure whereabouts this goes in my code posted above ?


Chris A

 

[villy]

you can insert that before For statements

 

[RoryA]

You haven't said what information from which sheet and how it applies?

 

[Chris11]

You haven't said what information from which sheet and how it applies?

Ok, So the numbers in the "IF" conditions (in first post) are being moved to "Sheet2" but all the cells that need the formatting are on Sheet1.

So I kind of need something like a lookup table so that the Sheet1 says

"Ok I have 8970 in sheet1 cell A3, let me check Sheet2 to see if that number (8970) has any conditional formatting so that I can colour it accordingly"

I hope that makes sense...haha.



Actually, reading this back, i'm not the sure the first piece of code is even what I want. As I'll need space for more numbers to be added. So maybe using Cell values in Sheet2 would be better than using dedicated numbers?

Thanks, and sorry for the confusion.

Chris

 

[RoryA]

I would use a table in sheet 2 with your numbers in with each number cell coloured whatever colour you want applied on the other sheet. Which range on each sheet is relevant?

 

[Chris11]

I would use a table in sheet 2 with your numbers in with each number cell coloured whatever colour you want applied on the other sheet. Which range on each sheet is relevant?

Ahh ok. So take off the conditional formatting altogether?

Urm, revelent ranges are.

Sheet1 = D6:N71
Sheet2 = A2:A40



Chris

 

[RoryA]

Something like this:

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 = Sheets("Sheet2").Range("A2:A40")
      For Each rngCell In Intersect(Target, rngMonitor)
         ColourCell rngCell, rngData
      Next rngCell
   End If
End Sub

Private 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

 

[Chris11]

Something like this:

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 = Sheets("Sheet2").Range("A2:A40")
      For Each rngCell In Intersect(Target, rngMonitor)
         ColourCell rngCell, rngData
      Next rngCell
   End If
End Sub
 
Private 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

Hmm, Ok I've tried using the above. But I cant get it to do anything? Cant even get it to show up as a module so that I can run it.

Think I'm missing something here....

Chris