S
Steve D
I've been trying to get this to work for hours, can anybody see where I'm
going wrong (by the way its my first dabble in trying to use colours in
VBA).
I want to put numeric values of say X and Y into A1 and A5 and then run a
function (fill_cell_sub) when I load the worksheet such that if X > Y, the
cells A6 to A11 are filled in with one colour (colour 1) but if X<Y they are
filled in with another colour that I define in the call.
Below is my current version.
Function fill_cell_sub(value1, value2, cell_index, colour_cell)
Dim i As Integer
Dim Range_new As Range
' Define the initial cell in the block and make it active
Range(cell_index).Offset(0, 0).Select
'
If (value1 > value2) Then
For i = 1 To 5
Set Range_new = Range(cell_index).Offset(-1 + i, 0)
Range_new.Select
Range_new.Interior.ColorIndex = 1
Selection.Interior.Pattern = xlSolid
Next i
ElseIf (value1 <= value2) Then
For i = 1 To 5
Set Range_new = Range(cell_index).Offset(-1 + i, 0)
Range_new.Select
Range_new.Interior.ColorIndex = colour_cell
Selection.Interior.Pattern = xlSolid
Next i
End If
'
End Function
Any help on where I'm going wrong would be much appreciated.
Steve D
going wrong (by the way its my first dabble in trying to use colours in
VBA).
I want to put numeric values of say X and Y into A1 and A5 and then run a
function (fill_cell_sub) when I load the worksheet such that if X > Y, the
cells A6 to A11 are filled in with one colour (colour 1) but if X<Y they are
filled in with another colour that I define in the call.
Below is my current version.
Function fill_cell_sub(value1, value2, cell_index, colour_cell)
Dim i As Integer
Dim Range_new As Range
' Define the initial cell in the block and make it active
Range(cell_index).Offset(0, 0).Select
'
If (value1 > value2) Then
For i = 1 To 5
Set Range_new = Range(cell_index).Offset(-1 + i, 0)
Range_new.Select
Range_new.Interior.ColorIndex = 1
Selection.Interior.Pattern = xlSolid
Next i
ElseIf (value1 <= value2) Then
For i = 1 To 5
Set Range_new = Range(cell_index).Offset(-1 + i, 0)
Range_new.Select
Range_new.Interior.ColorIndex = colour_cell
Selection.Interior.Pattern = xlSolid
Next i
End If
'
End Function
Any help on where I'm going wrong would be much appreciated.
Steve D