vb for coloured rows.

[dshilan]

Hi,

A while ago someone on here provided me with the below, but I didn't need it in the end, now I do!

It formats the colours of rows from cells B:R based on B...all good.

I'd like a few tweaks if possible:

1) I have to run the macro each time a change is made, is there a way to make it run itself everytime a cell in column B is changed?
2) It takes a while to run, as I assume it working it's way down through every row, is there a way to limit from say row 1 to 500?
3) General query - how do I assign a key command to run the macro.

Sorry for all the questions, hopefully if I can get this working I can apply this to a template and use it on many spreadsheets, saving me alot of time day to day!

Thanks,

Dan

Please Login or Register  to view this content.

[Andrew-R]

This should run a bit quicker:

Please Login or Register  to view this content.

You can have it run every time the workbook changes by:

1. Going into the VB Editor
2. Double-clicking on the ThisWorkBook object in the left-hand pane.
3. In the left-hand drop-down at the top of the text window select the "Workbook" option
4. In the right-hand drop-down at the top of the text window select "SheetChange"
5. A new sub routine will be created for the sheet change event, edit it to make it look like the following:

Please Login or Register  to view this content.

Job's a good 'un.

[dshilan]

Works like a charm.

Thanks a lot.