Hi All,
I'm having a difficult time getting this piece of code to work. For a variety of reasons I can't use conditional formatting.
My problem:
Each cell has a different value:
I simply want something that if the cell says "RED" the code will color that cell red etc. The total population of values is roughly A2:BZ146. The code I have at the moment doesn't cycle through cells properly and every cells ends up colored as whatever color is at the end of the code. I'm just using a test sheet at the moment for the range A1:A7 to try to get it to work.
Any ideas? Thanks!
I'm having a difficult time getting this piece of code to work. For a variety of reasons I can't use conditional formatting.
My problem:
Each cell has a different value:
- GREEN
- GREENAMBER
- AMBERGREEN
- AMBER
- AMBERRED
- REDAMBER
- RED
I simply want something that if the cell says "RED" the code will color that cell red etc. The total population of values is roughly A2:BZ146. The code I have at the moment doesn't cycle through cells properly and every cells ends up colored as whatever color is at the end of the code. I'm just using a test sheet at the moment for the range A1:A7 to try to get it to work.
Code:
Sub ColourChange()Dim rng As Range
Dim cell As Range
Set rng = Worksheets("RULES").Range("A1:A7")
For Each cell In rng.Cells
If cell.Value = "GREEN" Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13561798 'GREEN
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf cell.Value = "GREENAMBER" Then
With Selection.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 0
.Gradient.ColorStops.Clear
End With
With Selection.Interior.Gradient.ColorStops.Add(0)
.Color = 13561798 'GREEN
.TintAndShade = 0
End With
With Selection.Interior.Gradient.ColorStops.Add(1)
.Color = 10284031 'AMBER
.TintAndShade = 0
End With
ElseIf cell.Value = "AMBERGREEN" Then
With Selection.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 0
.Gradient.ColorStops.Clear
End With
With Selection.Interior.Gradient.ColorStops.Add(0)
.Color = 10284031 'AMBER
.TintAndShade = 0
End With
With Selection.Interior.Gradient.ColorStops.Add(1)
.Color = 13561798 'GREEN
.TintAndShade = 0
End With
ElseIf cell.Value = "AMBER" Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10284031 'AMBER
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf cell.Value = "AMBERRED" Then
With Selection.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 0
.Gradient.ColorStops.Clear
End With
With Selection.Interior.Gradient.ColorStops.Add(0)
.Color = 10284031 'AMBER
.TintAndShade = 0
End With
With Selection.Interior.Gradient.ColorStops.Add(1)
.Color = 13551615 'RED
.TintAndShade = 0
End With
ElseIf cell.Value = "REDAMBER" Then
With Selection.Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 0
.Gradient.ColorStops.Clear
End With
With Selection.Interior.Gradient.ColorStops.Add(0)
.Color = 13551615 'RED
.TintAndShade = 0
End With
With Selection.Interior.Gradient.ColorStops.Add(1)
.Color = 10284031 'AMBER
.TintAndShade = 0
End With
ElseIf cell.Value = "RED" Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13551615 'RED
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next cell
End Sub
Any ideas? Thanks!