Conditional formatting in Excel 2010

[RichardMGreen]

Hi all

I've set up some conditional formatting in Excel 2010 and it's working as I want it to in the template.
To create the fional report, certain sheets are copied to a new workbook and forced into values only (all this is done in VBA).

Unfortunately, the conditional formatting is keeping workbook links, so the formatting looks like this:-
=E16>='\\TRFTSTORE01\USERS_HOME\Data Warehouse Team\GreenRichard\Board Scorecard\[Performance Framework Data v1.2.xlsm]Reference'!#REF!
(the #REF is actually a proper cell reference in the template so I'm not sure what's happening with that).

Is there any way of keeping the colours of the conditional formatting without copying over the Reference sheet?


Any help would be gratefully received.

 

[theprincipal78]

hi there

check out the two links below as well as the two macro might be helpful.

VBA code examples for a List or Table in Excel 2003-2013</SPAN></SPAN>
http://www.rondebruin.nl/win/s5/win006.htm</SPAN></SPAN>


Table Tools Add-in for Excel 2007-2013</SPAN></SPAN>
http://www.rondebruin.nl/win/addins/tabletools.htm</SPAN></SPAN></SPAN>



Sub NameManager_DeleteBadReferences()</SPAN></SPAN>

Application.ScreenUpdating = False</SPAN></SPAN>
Dim nm As Name</SPAN></SPAN>
For Each nm In ActiveWorkbook.Names</SPAN></SPAN>
If InStr(1, nm.RefersTo, "#REF!") > 0 Then</SPAN></SPAN>
'List the name before deleting</SPAN></SPAN>
Debug.Print nm.Name & ": deleted"</SPAN></SPAN>
nm.Delete</SPAN></SPAN>
End If</SPAN></SPAN>
Next nm</SPAN></SPAN>
Application.ScreenUpdating = True</SPAN></SPAN>
End Sub</SPAN></SPAN>

Sub NameManager_DeleteNames()</SPAN></SPAN>

Application.ScreenUpdating = False</SPAN></SPAN>
Dim nm As Name</SPAN></SPAN>
For Each nm In ActiveWorkbook.Names</SPAN></SPAN>
'If InStr(1, nm.RefersTo, "#REF!") > 0 Then</SPAN></SPAN>
'List the name before deleting</SPAN></SPAN>
Debug.Print nm.Name & ": deleted"</SPAN></SPAN>
nm.Delete</SPAN></SPAN>
'End If</SPAN></SPAN>
Next nm</SPAN></SPAN>
Application.ScreenUpdating = True</SPAN></SPAN>
End Sub</SPAN></SPAN>



cheers.

 

[RichardMGreen]

It's not an issue with names (under Name Manager) as I've sorted those.
I don't want to delete the bad references, I just wanted to break the links but have the new workbook keep the colours from the conitional formatting.

As a (hopefully) temporary measure, I've adjusted my code to copy oacross the references sheet and hide it.