G
Guest
With the help of Mr. de Bruin and Mr. Dibben, I have successfully applied a color code to an aray of cells using the suggested code. The cells in seven columns have been colored based on their text value. Now I am trying to apply the same colors to the cells in an adjacent column regardless of their content. For instance, cell B8 is assigned a color based on its content and I want to apply the same color to E8 even though E8 may be empty. Then B9 and E9 etc. Any suggestions would be appreciated. I am currently using the following code
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,F8:F19,J8:J19,N8:N19,R8:R19,V8:V19,Z8:Z19")
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
Next rn
End Sub
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,F8:F19,J8:J19,N8:N19,R8:R19,V8:V19,Z8:Z19")
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
Next rn
End Sub