N
nejlangton
Hi,
I have a spreadsheet with different rows of data refering to seperat
issues etc. these are ranked either 1-5 or 1-7. the rank is held i
column s, however I want the 2nd cell in the row (column b) to chang
colour according to to the value in column s.
Given that there are more than 3 colours needed, i cant use norma
conditional formatting and so am trying to use the following macro. i
first targets column s and where values i then want it to target colum
b in the same row and change the colour according to the value.
I would be really grateful for any help.
Cheers
Nejl
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myColor As Long
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("s:s")) Is Nothing Then Exit Sub
Select Case LCase(Target.Value)
Case Is = "1": Target.Cell("b, 0").Interior.ColorIndex = 3
Case Is = "2": Target.Cell("b, 0").Interior.ColorIndex = 46
Case Is = "3": Target.Cell("b, 0").Interior.ColorIndex = 6
Case Is = "4": Target.Cell("b, 0").Interior.ColorIndex = 4
Case Is = "5": Target.Cell("b, 0").Interior.ColorIndex = 34
Case Else
myColor = xlNone
End Select
Target.Interior.ColorIndex = myColor
End Su
I have a spreadsheet with different rows of data refering to seperat
issues etc. these are ranked either 1-5 or 1-7. the rank is held i
column s, however I want the 2nd cell in the row (column b) to chang
colour according to to the value in column s.
Given that there are more than 3 colours needed, i cant use norma
conditional formatting and so am trying to use the following macro. i
first targets column s and where values i then want it to target colum
b in the same row and change the colour according to the value.
I would be really grateful for any help.
Cheers
Nejl
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myColor As Long
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("s:s")) Is Nothing Then Exit Sub
Select Case LCase(Target.Value)
Case Is = "1": Target.Cell("b, 0").Interior.ColorIndex = 3
Case Is = "2": Target.Cell("b, 0").Interior.ColorIndex = 46
Case Is = "3": Target.Cell("b, 0").Interior.ColorIndex = 6
Case Is = "4": Target.Cell("b, 0").Interior.ColorIndex = 4
Case Is = "5": Target.Cell("b, 0").Interior.ColorIndex = 34
Case Else
myColor = xlNone
End Select
Target.Interior.ColorIndex = myColor
End Su