I have various sheets that have columns of data.

What I want to do is make another sheet that copies the data from
certain columns and inserts them in the new sheet.

I've tried a simple =Sheet1!A1 then dragged the formula down the rows.
My problem is that I have a simple formula for changing the background
color of the cell based on the input, but the in the copied sheet the
background doesn't change.

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions

Dim rng As Range

Set rng = Intersect(Target, Range("A1:Z5000"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case "h"
cl.Interior.ColorIndex = 3
Case "f"
cl.Interior.ColorIndex = 4
Case "ba"
cl.Interior.ColorIndex = 32
Case "bh"
cl.Interior.ColorIndex = 33
Case "h/2"
cl.Interior.ColorIndex = 44
Case "f/2"
cl.Interior.ColorIndex = 35
Case "s"
cl.Interior.ColorIndex = 15
Case "l"
cl.Interior.ColorIndex = 6
Case ""
cl.Interior.ColorIndex = none
Case "c"
cl.Interior.ColorIndex = 7

Case Else
Exit Sub
End Select
Next cl
End If

Any ideas?


--
teeb
------------------------------------------------------------------------
teeb's Profile: http://www.excelforum.com/member.php...o&userid=13172
View this thread: http://www.excelforum.com/showthread...hreadid=555498