Forum Discussion
Conditional Formatting on Concatenate Formula
milo1234 there was a typo in one of the formulas that references row E instead of H. I fixed that so I hope the attached works for you.
As for copy and paste, yes it will "change" the applied to and the formula but that should be ok. What it is doing is COPYING both the applied to range and formula and shifting the cell references according to the new location. You can see that if under conditional formatting you use the drop-down at the top and set it to "This Worksheet" and you will see 'duplicate rules'. And after a bit of copy, paste, insert, and such that conditional formatting drives my OCD crazy and I have to 'clean it up' but that is cosmetic and not functional issues.
mtarler Thank you for this. I have applied the formula to all relevant columns with the correct colour coding. It seems to work for most of the cells, however in the sample data attached, if I change a cell to F1 and T1 this should be green together, however one cell shows (F1) as green and the other cell as red (T1).
Also when I copy the 3 columns and insert these copied cells for a new week, the cells that the conditional formatting applies to, changes.
Please can you help?
milo1234 there was a typo in one of the formulas that references row E instead of H. I fixed that so I hope the attached works for you.
As for copy and paste, yes it will "change" the applied to and the formula but that should be ok. What it is doing is COPYING both the applied to range and formula and shifting the cell references according to the new location. You can see that if under conditional formatting you use the drop-down at the top and set it to "This Worksheet" and you will see 'duplicate rules'. And after a bit of copy, paste, insert, and such that conditional formatting drives my OCD crazy and I have to 'clean it up' but that is cosmetic and not functional issues.
mtarler Thank you again, this is really useful.
I can see you resolved the formula, however when you copy columns BH, BI & BJ for a new week and insert the copied cells, the same issue applies.
T1 and F1 does not change to green together - what can I do to stop this?
- the easiest is to not "insert copied cells", just PASTE. If you need to insert columns then do that 1st then copy and paste. That is interesting what "insert copied cells" does, not sure if that is a bug or not and might be useful trick in certain cases.