insert blank rows based on columns a values numerically

Hello , I work on university and take the colleges results to format it , I have the following

before:

column A........column B.......column C......column D
3002................23................53.5.............29.5
3005................33................58................30
3007................32................48................30
3010................35................36................30
3011................20................38.5.............20.5
3013................55................30...............47
3014................16.5.............18.5.............28
3016................33................45.5.............30

column (a) represent seating number on what I arrange the rows on it in the work sheet , the missing numbers between (3002) and (3005) are absent students ,
what I want is a macro to check column a , if the difference between for example 3002 and 3005 is 3 numbers then it insert 3 blank rows and write the missing seating number (3003,3004) in it , and if the difference for example only one number inserting one row and so on and mark the inserted value as red .

after:

column A........column B.......column C......column D

3002................23................53.5.............29.5
3003
3004
3005................33................58................30
3006
3007................32................48................30
3008
3009
3010................35................36................30
3011................20................38.5.............20.5
3012
3013................55................30...............47
3014................16.5.............18.5.............28
3015
3016................33................45.5.............30

I use office 2007

Any help is appreciated , Thanx in advance .

No need for macro this is easily doable with functions and conditional formatting see attached