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