Conditional formatting problem with 'applies to' when expanding a range

Hi,

I have recently upgraded to Excel 2010 and I have come across a really annoying problem that I did not notice before in previous versions.

I have a VBA application where I want to allow the user to add a new record to a series of tables but I want the new row to be appended at the end of the table rather than somewhere in the middle.

To do this, I navigate to the end of the table and expand the range to incorporate the newly inserted row.

The problem is the conditional formatting 'applies to' range is not automatically expanded when I do this. Instead, a new rule is created just for the new row. If several new rows are added, a separate rule will be created each time and this will be very messy and difficult to maintain.

Please see spreadsheet attached to illustrate this problem.

I want to avoid inserting the row somewhere in the middle (which I know expands the 'applies to' range) automatically.

I tried to fix the problem by entering the range name (=DeptList) in the 'applies to' field but when i do this, the $D$5:$D$10 reference style is re-applied and I can't change this.

If someone can help me with this, I would be very grateful.


GTOL

Hi Venkat,

Many thanks for your reply.

I have conditional formatting in the example already as follows :
=COUNTIF(DeptList,C5)>1

This checks for duplicate entries in the table as a validation check. If the same value is entered more than once, all cells with that same value will be highlighted in red.

I have not used VBA for this conditional formatting but I am using VBA to manage how new rows are added to the table. My problem is that the 'applies to' range is not expanded when a new row is added to the end of the table.

This was not a prroblem when I did this before in Excel 2003. Maybe there is a bug in Excel 2010 or, more likely, it's something to do with the way the rules are applied ???

Any help would be appreciated.


GTOL