VBA code help

[jamie.gill]

I am creating a customer database in excell that identifies customers on a plan via a reference number.
A plan has a reference number and on the spreadsheet and all customers on a plan are input next to each other on a spreadsheet at the same time.

E.g
[TABLE="width: 331"]
<tbody>[TR]
[TD]Plan number[/TD]
[TD]customer number[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]21689[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]54863[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]35489[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD="align: right"]36547[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD="align: right"]25418[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]12458[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]36985[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]32154[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]32546[/TD]
[/TR]
[TR]
[TD="align: right"]98[/TD]
[TD="align: right"]22144[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]25222[/TD]
[/TR]
[TR]
[TD="align: right"]56[/TD]
[TD="align: right"]36887[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]23569[/TD]
[/TR]
</tbody>[/TABLE]
To group these by back ground i entered a formula into a cell on the far right of the data range with the formula =IF(B2=B1,E1,MOD(E1+1,2)) so if this returns 1 that row turns blue by conditional format. This also ensures all plans block color as if the plan code doesn't change the color wont.

however if i insert a row to add a new customer to a plan this code doesn't copy... is there a macro i can make which takes this formula and copies it all the way down the range of the data (but not beyond)... i.e. it analyzes how much data is in the spread sheet and only copies it to that length.

Thanks

 

[Smitty]

If you set your data up as a table, then your formulas will autofill when you add a new row (tables are dynamic, so Excel always knows how big they are).

HTH

 

[jamie.gill]

=IF(B9=B7,E7,MOD(E7+1,2))

This is inserting a rows effects on the formula in the cell bellow it stretches it

 

[jamie.gill]

so it doesnt keep changing the formats on the spreadsheet... i hope that's clear as to my dilema?