Making code run when all cells in a range contain anything

[Lobsterboy1]

Hi,

I am trying to make a macro run when all cells in a range (E2:G2) contain any value. E2 will be a date typed in manually F2 and G2 will be picked from a drop down list.

Here is my code

Sub add_rows_at_top()
 With Application
         .Calculation = xlCalculationManual
         .ScreenUpdating = False
    Range("a2").EntireRow.Insert
    Range("a3").EntireRow.Copy
    Range("a2").PasteSpecial xlPasteAll
    Rows("2").EntireRow.ClearContents
    Range("A3:C12").Select
    Selection.Cut
    Range("A2").Select
    ActiveSheet.Paste
        .Calculation = xlCalculationAutomatic
         .ScreenUpdating = True
  End With
End Sub

The code is probably not the best but I am open to changing it to something neater.

Here is my sheet.

Skills log

*	A	B	C	D	E	F	G
*
J.Smith	*	*	*	*
A.Sugar	*	*	*	*
N.Mansell	*	*	*	*
A.Shearer	*	*	*	*
T.Hardy	*	*	*	*
*	*	*	*	*	*	*
*	*	*	*	*	*	*
*	*	*	*	*	*	*
*	*	*	*	*
*	*	*	*	*
*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:148px;"><col style="width:144px;"><col style="width:117px;"><col style="width:64px;"><col style="width:106px;"><col style="width:218px;"><col style="width:187px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]NAME[/TD]
[TD="align: center"]DATE OF LAST TRAINING[/TD]
[TD="align: center"]NO OF RECORDS[/TD]

[TD="align: center"]DATE OF TRAINING[/TD]
[TD="align: center"]NAME [/TD]
[TD="align: center"]SKILL[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #ff0000, align: center"]00/01/1900[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #ff0000, align: center"]00/01/1900[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #ff0000, align: center"]00/01/1900[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #ff0000, align: center"]00/01/1900[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #ff0000, align: center"]00/01/1900[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #ff0000"]*[/TD]
[TD="align: center"]DATES HIGHLIGHTED RED IF OVER 30 DAYS SINCE LAST TRAINING RECORD[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #ff0000"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 {=MAX(IF(A2=F:F,E:E))} C2 =COUNTIF($F$2:$F$5001,A2) B3 {=MAX(IF(A3=F:F,E:E))} C3 =COUNTIF($F$2:$F$5001,A3) B4 {=MAX(IF(A4=F:F,E:E))} C4 =COUNTIF($F$2:$F$5001,A4) B5 {=MAX(IF(A5=F:F,E:E))} C5 =COUNTIF($F$2:$F$5001,A5) B6 {=MAX(IF(A6=F:F,E:E))} C6 =COUNTIF($F$2:$F$5001,A6) <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Basically what I am wanting to do is

1, Have cells E2:G2 empty.
2, When I enter a date in E2, Pick from the drop down lists in F2 and G2. once all 3 of these cells are complete the code runs.
3, The code adds a new row between rows 1 and 2 and makes ready for the next entry.

This instead of when there are a lot of entries scrolling down hundreds of lines to add the next entry, the latest ones are always near the top.

Cheers for any help.