Excel macro help please

Hi guys,

I have a excel document with several If statements. Everything I have figured out so far. But now I am sitting with a problem. The 1 sheet is about 10 - 15 pages long with info that has to be filled in (it's a compliance audit that the company will be doing)

Now they can score 1/2/3 with the compliance. Also the compliance itself is a A/B/C

The score is worked out as follow:

Compliance Score * Compliance Level

So it is 1/2/3 * A/B/C (where A=3, B=2, C=1)

This has been done. It also give you the total score out of the total compliance per section and an overall score.

The total for each section will display as follow (using If statements and not conditional formatting):

It automatically changes colour (to red) if the compliance score is <40%.
It automatically changes colour (to yellow) if the compliance score is >=40% <80%.
It automatically changes colour (to green) if the compliance score is =<80%.

Then I can also extract all the sections to a pre-defined sheet (where all the sections are 1 - 10) and only changes the total value and colour. This is the problem They don't want the Section value...

Lets say section one looks like this:

1. Heading


1.1 Is it safe in the workplace? 1 A
1.2 Is it clean In the workplace? 3 A
1.3 Are the employees neat? 0 C
1.4 Are the water taps running? 3 C


So as you can see the cells in red has to be "copied" to an different sheet. The Section number, Section question, Question score and the compliance level has to be "copied."

I don't know how to look for an empty row and use it. If it's full then go to the row just below that one. If it goes over to section 2 to maybe leave 1 row open and carry on then.

Is there a VBA thing I can add to do this? Or what should I do?

Thanks guys