Excell - Sorting and conditional formatting

[Evening]

I have an Excell file entering some records which I want to sort based on some columns. I made a program in VBA for the following:

All records entered in worksheet # 1:
A B C D
1 x x x x
2 y y y y
.
1000 z z z z

On worksheet# 2 I have a copy of worksheet # 1 , (every field is just reading worksheet #1) and making some reports based on sorting based on columns A, B, C, etc…. with VBA program. The worksheet is protected in order to prevent any row deletion.

Problem # 1:
The problem is that on worksheet #1 I need to delete and insert sometimes some rows, what will imediatelly brake the chain in worksheet #2 , which is reading from sheet #1.

Problem #2:
In worksheet #1 I used a conditional formatting, (background color and frame lines) .
The conditions are the following:

Condition # 1:
=AND ( NOT($A10=$A9)), ($A10=$A11) ) Use top frame line, not bottom frame line

Condition #2:
=AND (NOT($A10=$A9)) use grey background colour


The problem is as soon as I add new row,for example in front of row 10 (so row 10 becomes row 11, ) , the condition now is row 11 become

=AND ( NOT($A11=$A9)), ($A11=$A12) ) Use top frame line, not bottom frame line

Condition #2:
=AND (NOT($A11=$A9)) use grey background colour

so it is not comparing the value with a previous row but 2 rows before.

What other way can I use in Excell to achieve my goal?

Any help will be appreciated

 

[combo]

#1:
You have decide when to temporarily break link between worksheets #1 and #2. VBA has to copy data from #1 to #2 and Private Sub Worksheet_Change(ByVal Target As Range) event can be a good starter to control transfer.

#2:
You can use only relative references combined with OFFSET and use only formula cell reference, for instance in A10:
=(A10=OFFSET(A10,-1,0))

combo