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

Why not use conditional formatting? See
http://www.contextures.com/xlCondFormat01.html

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Steve D" wrote in message
...
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

You basically have the right idea. However a function can only the the value
of the cell into which it is entered. It cannot change formats. If you want
to modify formats you can:

1. use conditional formatting
2. use a sub type macro
--
Gary''s Student - gsnu2007


"Steve D" wrote:

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