I need a formula to fill x amount of cells with a number or colour as i
enter x number into a spreadcheet.
what i am trying to do is a line rota.
where i put, for example, between 1200hrs & 1300 i need 6 people on, so
therefore would like 6 cells in a colum highlighted., if i change the 6 to a
3 then the highlighted cells would reduce to only 3.
I'm using excel 2003

many thanks and plse help!!

Try this:

For example, Cell B2 contains the number of people you need, and you want
the rows below to fill in according to the amount of people you need:

Select Cell B3, then on the menu, choose Format, and then Conditional
Formatting.

Condition 1

In the drop down box, choose formula is (instead of Cell Value is)
In the box to the right, type =B21
Then click on Fomat, and pattern to choose a colour.
Click ok

Then you need to add to this by clicking Add

Condition 2
Do the same, but put =B2=1

Then in cell B4, repeat the same as above for condition 1 only, but change
it to B22.

Repeat this for the cells -

Cell B5 - B23 and so on.










"kielp" wrote:

I need a formula to fill x amount of cells with a number or colour as i
enter x number into a spreadcheet.
what i am trying to do is a line rota.
where i put, for example, between 1200hrs & 1300 i need 6 people on, so
therefore would like 6 cells in a colum highlighted., if i change the 6 to a
3 then the highlighted cells would reduce to only 3.
I'm using excel 2003

many thanks and plse help!!

Kelp,

This code will colour a number of cells immediately below the cell
containing the changed value. So, if you enter "6" into A1, A2:A7 will be
coloured, if you enter "4" into B3, B4:B7 will be coloured and so on.
If you change A1 to "4", A2:A5 will be coloured, if you cnange B3 to "9",
B4:B12 will be coloured, and so on.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NoOfCellsToFormat As Integer
NoOfCellsToFormat = Target.Value

On Error GoTo ws_exit:
Application.EnableEvents = False

'Clear any previous background colour from the column
Target.EntireColumn.Interior.ColorIndex = xlNone
'Format the range of cells starting 1 cell below the cell that has changed
'to have a fill color of 8
Target.Offset(1, 0).Resize(NoOfCellsToFormat, 1).Interior.ColorIndex = 8

ws_exit:
Application.EnableEvents = True
End Sub

Hope this helps, but if not, I'm sure you can modify it to be what you need.

Regards

Pete



You must paste it into the code window of the worksheet itself, NOT a normal
macro sheet, for it to work.

"kielp" wrote:

I need a formula to fill x amount of cells with a number or colour as i
enter x number into a spreadcheet.
what i am trying to do is a line rota.
where i put, for example, between 1200hrs & 1300 i need 6 people on, so
therefore would like 6 cells in a colum highlighted., if i change the 6 to a
3 then the highlighted cells would reduce to only 3.
I'm using excel 2003

many thanks and plse help!!