Frank
I have two sheets that are identical except for the page layout. In each sheet I have code that formats the cell background color based on the text value in the cell. I want to copy exactly the value in each filled cell in sheet1 into the corresponding cell in sheet2 and then hopefully allow the same formatting code set the cell color in sheet2. My hope is to be able to enter the information in sheet1 and have sheet2 automatically look the same. This is the code I have already

Private Sub Worksheet_Change(ByVal Target As Range
Dim Num As Lon
Dim rng As Rang
Dim vRngInput As Varian
Set vRngInput = Intersect(Target, Range("B8:B19,D8:D19,F8:F19,H8:H19,J8:J19,L8:L19,N 8:N19")
If vRngInput Is Nothing Then Exit Su
For Each rng In vRngInpu
'Determine the colo
Select Case rng.Valu
Case Is = "SSH": Num = 3
Case Is = "SMH": Num = 3
Case Is = "SSO": Num = 2
Case Is = "SKMH": Num = 3
Case Is = "SA": Num = 4
Case Is = "SBC": Num = 4
Case Is = "HC": Num = 3
Case Is = "ADMIN": Num = 5
Case Is = "OC": Num = 1
End Selec
'Apply the colo
rng.Interior.ColorIndex = Nu
rng.Offset(0, 1).Interior.ColorIndex = Nu
Next rn
End Sub