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]

It goes in the code module for Sheet1. Then if you change a value anywhere in the range D6:N71, it will find the cell on sheet2 that contains that value and apply that cell's fill colour to the cell on Sheet1 (if the value is not found, it clears the colour)

 

[Chris11]

It goes in the code module for Sheet1. Then if you change a value anywhere in the range D6:N71, it will find the cell on sheet2 that contains that value and apply that cell's fill colour to the cell on Sheet1 (if the value is not found, it clears the colour)

Hmm Saying there is an error on this line:

Set rngData = Sheets("Sheet3").Range("A2:A40")

 

[RoryA]

Then I guess you don't have a sheet called Sheet3.

 

[Chris11]

No, I do!!
I changed it from Sheet 2 as all the values are now in Sheet3.

 

[RoryA]

What's the error? Subscript out of range?

 

[Chris11]

Yup,

"Run-time error '9':
Subscript out of range"

 

[RoryA]

Then you do not have a sheet called Sheet3.

 

[Chris11]

Then you do not have a sheet called Sheet3.

DERP.

You are correct, I thought because it was called Sheet3 (Build Variables) I could just call it sheet3.

Cheers man, you're a friggin genius...

(All works now)

Thanks,

Chris A

 

[RoryA]

Since you know what a codename is, I'd actually suggest you use it in preference to the name:

Set rngData = Sheet3.Range("A2:A40")

 

[Chris11]

Since you know what a codename is, I'd actually suggest you use it in preference to the name:

Set rngData = Sheet3.Range("A2:A40")

haha, Yeah cool, 'cos then if someone changes the Sheet name its not gonna screw everything up.

Thanks Again.

Chris