the colours to be changed when rows are added or deleted

Originally Posted by mtgman

I am a branch manager of a mortgage company. I created a spreadsheet to better track the clients for my branch between myself and my processor. I created a spreadsheet that is 12 columns wide and 34 long. I have chosen 5 colors that I alternate each row and repeat the colors throughout the 34 rows. My question is this, say that the client that is listed on row 5 closes, I want to be able to delete the data in that row and have the 6th row become the 5th and so forth. Hopefully this can work the other way as well so if I wanted to insert a row. My wife seems to think it can;t be done. My neighbor on the other hand (who is typically right) thinks this can be accomplished.

Any help is greatly appreciated.

Thanks
Kris

Yes it can be done.


Right click on the row number then click on delete.

And you can do the some for the column click on the A col and delete. B -> A

To add right click then select insert.

If I understand your problem correctly you want the colurs to be changed when rows are added or deleted

It can be done with a macro as long as you do not have Macro Security set to high or set a digital certificate for the macro

This macro will recolour the cells in column A to L from row 2 to the last used row with an entry in column A

change the colour numbers of the command shown in Red to suit your requirements

'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy this macro
'GoTo Excel
'Select sheet this is to appy to
'Right Click on Sheet Name Tab > select View Code
'Past macro into the Worksheet Module displayed

Please Login or Register  to view this content.

[COLOR="Blue"]Note:- I forgot to mention the macro will only work if 1 row is added or deleted at a time[/COLOR]



This line of code says from column A to column L if you can change the l inside the " marks to another column as required

example changing

Please Login or Register  to view this content.

from above to

Please Login or Register  to view this content.

will colour cells A to W


or to colour the entire row replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

If the above does not do as required then you can post it to this site when you reply.
To post the file as an attachment to this site it needs to be zipped & less than 100kb. If it is bigger than 100kb then you could upload it to a file sharing site like rapidshare.com & post a link with a reply to this thread.

Macro added to the workbook attached to this reply

Macro will run if single/multiple rows are added/deleted and also
if the last used row in column A is not the same row number of the last used row in Column C

Macro removes border & cell colouring formating below the last used row
Macro adds border if the last used row in column A is not the same row number of the last used row in Column C

Macro formats from column A to last used column based on headers in row 3. this is currently column W

Macro can easily be modified to allow for changes to the number colours