GeorgeF211
New Member
Hi All,
I recently came to the conclusion that my spreadsheets just aren't colourful enough. To that end, I decided to bodge out some VBA code to give me a full range of cell shading from RGB(1,255,1) to RGB(255,1,1) - increasing red by one each iteration and decreasing green by one. Perhaps unsurprisingly, this has crashed excel. Does anyone know of a more efficient way of giving myself a good gradient of colours, or should I just stick to some of the more basic 5 to 10 colour options with set conditions.
[pre]
[/pre]
Any help would be greatly appreciated,
Cheers
I recently came to the conclusion that my spreadsheets just aren't colourful enough. To that end, I decided to bodge out some VBA code to give me a full range of cell shading from RGB(1,255,1) to RGB(255,1,1) - increasing red by one each iteration and decreasing green by one. Perhaps unsurprisingly, this has crashed excel. Does anyone know of a more efficient way of giving myself a good gradient of colours, or should I just stick to some of the more basic 5 to 10 colour options with set conditions.
[pre]
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Set ranger = Range("E1:E100")
Dim Red As Integer 'Red component of colour
Dim Green As Integer 'green component of colour
Dim Blue As Integer 'blue component of colour
Red = 1
Green = 255
Blue = 1 'we're not using this right now, but it might come in handy later
'these three statements set the colour to pure green
For Each cell In ranger 'run for the entire range
If Not IsError(cell.Value) And IsNumeric(cell.Value) Then 'only work on non-error numeric cells
Do Until Red = 255 And Green = 1 'keep running until we are at pure red
If cell.Value > Cells(ActiveCell.Row, 3) * Red / 255 Then
'Put whatever we're going to be doing with the colours here
cell.Interior = RGB(Red, Green, Blue)
If Green > 1 Then 'Roll the green back until it hits one
Green = Green - 1
End If
If Red < 255 Then 'move up through the red values until it hits 255
Red = Red + 1
End If
'I've kept these seperate as it allows you to set different starting points - also prevents errors
End If
Loop
End If
Next
End Sub
Any help would be greatly appreciated,
Cheers