Re: Color Range Based On Cell Value
Hi, (thanks for the reply kneejerk), from various research I have almost got a "cleaner" solution:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub 'Limit Target to one cell Dim icolor As Integer Dim c As Integer Dim rang1 As Range If Not Intersect(Target, Range("C1:C20")) Is Nothing Then Select Case Target Case "R": icolor = 3 'Red Case "r": icolor = 3 'Red Case "Y": icolor = 6 'yellow Case "y": icolor = 6 'yellow Case "G": icolor = 4 'green Case "g": icolor = 4 'green Case "B": icolor = 5 'Blue Case "b": icolor = 5 'blue Case Else: icolor = xlNone End Select Target = UCase(Target) Target.Interior.ColorIndex = icolor 'rang1(Target.Column.Offset(0, -2), Target.Column.Offset(0, 9)).Interior.ColorIndex = icolor End If End Sub
but you will see i have stumbled on the commented line (rang1) can anyone get me over the last hurdle? I can colour the target cell but can't extend to the range I need..