Conditional Formatting Equation

[Coyotyto]

I have a worksheet where I am trying to apply conditional formatting. I want to apply the equation

=($C$6/'Q1 2016'$C$6)>$H$39

I am trying to apply this to my entire worksheet but it will only work for the cell C6. The entire range I am using is from C6 to AB10. How do I have this code which divides the cell by the cell in 'Q1 2016' and checks if it is less than the value of H39 which I do not want to change.

Thanks,

 

[JoeMo]

Select all the cells you want to format (C6:AB10), then apply this rule (note relative reference):

=(C6/'Q1 2016'$C$6)>$H$39

 

[Mackers]

Hi

I think you just need to tweak the absolute values in your formula, being the dollar signs surrounding the cell. For example, A2 is not absolute, A$2 is absolute in the row (i.e. the A can change but the 2 can't), $A2 is absolute in the column (the A can't change but the 2 can) and $A$2 is absolute in every direction (it won't change no matter how you drag your cell around).

First click in a single cell, let's say C6. Write the formula as you want but make sure that target cell C6 it is checking does not have any dollar signs. Then paint this formula to your whole sheet - the target cell should change but the $H$39 should not.

Hope that helps

Mackers

 

[Eric W]

If you want the cell on the current sheet divided by the equivalent cell on Q1 2016, (C6/'Q1 2016'C6, C7/'Q1 2016'C7, etc.) then use this formula with the dollar signs removed from both C6 references:

=(C6/'Q1 2016'C6)>$H$39

 

[Coyotyto]

When I changed the $ sign reference the Conditional Formatting Applied as it should. My next issue is that I have a macro written inside of this workbook that will import the Quarterly Reports that I need to be analyzing the 'Q1 2016'. When I run the macro to import new Quarters it messes up the conditional formatting code I originally wrote leaving a #REF error. Going forward is there something I can change about the formula to correctly use the new Spread Sheet or will I need to write another Macro to fix this.

If it helps the worksheet with the conditional formatting will not be changed and the second worksheet I will be using in the equation will always be the first sheet in the Workbook.

Thanks,