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
1	NAME	DATE OF LAST TRAINING	NO OF RECORDS	*	DATE OF TRAINING	NAME	SKILL
2	J.Smith	00/01/1900	0	*	*	*	*
3	A.Sugar	00/01/1900	0	*	*	*	*
4	N.Mansell	00/01/1900	0	*	*	*	*
5	A.Shearer	00/01/1900	0	*	*	*	*
6	T.Hardy	00/01/1900	0	*	*	*	*
7	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*
10	*	DATES HIGHLIGHTED RED IF OVER 30 DAYS SINCE LAST TRAINING RECORD	*	*	*	*	*
11	*	*	*	*	*	*
12	*	*	*	*	*	*

<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>
</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.