Forum Discussion
Conditional Formatting
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 ...
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.
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.