Conditional Formatting Rows, and Matching and Sorting Two Sets of Data

Hi

I want to match and sort two groups of data as shown in the ‘Before’ and ‘After’ worksheets in the attachment.

Basically, there are twenty students in a class with related information (ID, Gender and Tutor).

At Checkpoint 1, tutors provide a Projected Mark based on the students’ performance to date. This data is sorted by the Projected Mark and colour coded. At Checkpoint 2 tutors provide a revised Projected Mark based on the students’ progress. This data is also sorted by Projected Mark and colour coded. See the ‘Before’ worksheet for details.

I want to be able to keep the Checkpoint 1 data as is and move the Checkpoint 2 data so that it is in line with the ID in Checkpoint 1. See the ‘After’ worksheet for details.

Can someone please provide me with:
1. A formulae for Conditional Formatting that colour codes rows according to the Projected Mark (Red is < 40, Orange is ≥ 40 and ≤ 79, Light Green is ≥ 80 and ≤ 119, Dark Green ≥ 120).
2. A formula that will enable me to move the Checkpoint 2 data so that it is in line with the ID in Checkpoint 1

Thanks in advance and looking forward to seeing your responses

Alan