Conditional Formatting Reference Error

[Coyotyto]

I currently have Conditional Formatting using the Formula:

=(C6/'Q1 2016'C6)<-$H$39

That applies to a worksheet. The Issue I am having is that the worksheet I am using in the Formula 'Q1 2016' Is one that I import into my Workbook through a Macro. So when I run through entering a new Quarters data the Condtional Formatting becomes:

=(C6/#REF!C6)<-$H$39

I am wondering If there is something I can manipulate with the formula to not have to write another Macro to check for this conditional formatting. But if so how would I go about creating the macro in VBA.

Some things that may Help the worksheet I am using the conditional formatting in will not be changed and is call "Calculations". The worksheet for the Quarter data that I am using in my Formula in this case 'Q1 2016' will always be the first worksheet in my workbook.

Thanks,

 

[StephenCrump]

When it is time to bring in data for Q2 2016, how does your macro do this? Can you show us the code?

It should be possible to rename the 'Q1 2016' sheet as 'Q2 2016', so that your conditional formatting formula (and presumably there must be other formulae referencing this sheet?) still work.