Dear all I found out that in the sample sheet I prepared there was no problem with the copying of the formula but due to the complexity of the original sheet (over 300 Lines) somehow there was no auto-refresh of the conditional formatting.
For this I found the answer in the microsoft forum:


http://answers.microsoft.com/en-us/o...d-47cbfe9378d0

credit to the original poster:

-------------------------------------------------------------------------------------------------------
Solution for the auto refresh problem with conditional formatting in Excel 2007
Koen replied to Paul Strike
12-Feb-10 12:17 PM

1. Just set up your conditional formatting in the cell's you want.
2. Make sure you activate the "Developer" tab in the menu.
3. Do this by clicking on the Office button, click on Excel options and in the "Popular" section check the box ""Show developer tab in the Ribbon".
4. Click on the Developer menu and go to the Visual basic view.
5. Click on the menu View and select "properties Window". In that list you can find the "EnableFormatConditionsCalculation".
6. Set this value to "True" and the auto refresh will work.
7. Perform this action for all tabs in your sheet.
-------------------------------------------------------------------------------------------------------



Thank you anyway for your help