Conditional Formatting - Dynamic Range from a specific cell

Hello Everyone
So my problem is that i have 2 different sheets. In Sheet 2 where the Conditional formatting is going to happen i want to format the range E3:O21 as shown in Sheet 1 with the formula: =$A3>0 (fill Red), but because the number of dimension e.g Account ID, Account type might change the Data that i want to have formatted might change aswell.
To illustrate this say that another Dimension, such as Product ID is being added, this happens through a Cube/data Mart extraction, where excel simply pushes all data to the right instead of inserting a new column. This means that i'll have Account ID, Account Type, Product ID, whereby the data (that i want formatted), will NOT start in E3:O21, but F3:P21, but he formatting still apply to E3:O21
To avoid having to drag the new range in the conditional formatting i made a cell in Sheet1 that automatically updates with the correct data range.
My Question is therefore if it's possible to make the cell in sheet1(containing the range) the range for the formatting? i tried to use indirect, but in the "applies to" field it wont let me use any formula's

Hope someone can help

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

If you insert columns at column D on sheet 2... the CF moves across with the relevant cells. Where's the problem??

Just a thought - what happens if you apply the formatting to the preceding column (C)? I think it should be applied to D if a column is inserted, but am not sure (don't have time at the moment to check it).