Conditional Formatting

[JoshArmorGalv]

I am currently upgrading a sheet for our production team.

A simple planning sheet and I am inserting conditional formatting into the template.

Original formula is =$D$7="Complete" this would change the text colour of cells $B$7:$D$7, the problem I have run into is when I had to copy and paste this formula to apply it to other ranges within the sheet. The range the the formula applies to changes with each copy and paste but the original D7 cell remained constant.

So I changed D7 from an absolute to a relative reference, which seemed to solve my issue, however when I tested the conditional formatting the change in text colour is now only applied to the first cell in the each range, so the formula =D7="Complete" applied to the range $B$7:$D$7 only changes the colour of the text in cell B7.

So I went through and changed all of the conditional formatting back to absolute references for every conditional formatting formula for every range. However now when I copy the first group of ranges and paste a second group the absolute references remains rather than changing for each new line.

Is there a fix for this? I really don't want to have to manually change 525 conditional formatting rules in every group of ranges!

[#2]

yes and no. there is no magic that will treat the $D$7 as relative when copying but absolute for the conditional formatting.
That said, maybe we can make the formula smarter to do what you need/want.
For example if you are copying down to every row so row 8 would be $D$8 you only need the $ on the column D for what you are doing so the row (7) can be relative and change. So:
=$D7="Complete"
will "hold" column D but let row 7 change so the range B7:D7 will always look at column D and hence D7 in each of those cases and when copied to row 8 it becomes D8. In FACT you can apply the conditional formatting to the entire columns B:D and use that single formula:
=$D7="Complete"
because as it check the following rows it will increment the relative row value accordingly.
If this doesn't work for you because it is more complicated, maybe include a sample sheet and more details and we can see what might work.