I am looking for a better way to conditionally format a cell, if its contents match any cell from another column (in a different tab or sheet).
Edit: I need a solution that will not be overwritten by copy/paste of a new cell without CF rules.
The method I currently have in place is via Sheet's internal conditional formatting settings. However it isn't completely successful because rows are often created and deleted copy and pasted over the range, overwriting or breaking apart the range that the conditional rule is applied to. The conditional formatting rule I have tried is:
=VLOOKUP(D2:D,INDIRECT("Blocked!$A$2:$A"),1,FALSE)=D2:D
This works until I insert a new row into the range that it is applied on. It will then only be applied to D2:D5 and D7:D1000, etc, for a row created at 6. Over time this leads to 100's of conditional formatting rules and large gaps of to where it is being applied.
It is my understanding that this needs to be done with Apps Script, to be done completely, and I am not sure where to begin.
Keeping the "Blocked" list of cells as a separate sheet would also be acceptable.
CodePudding user response：
Should be pretty straightforward. New tab on your shared sheet called MK.Idea with this cond. formatting rule:
=ISNUMBER(MATCH(D1,INDIRECT("Blocked!A:A"),0))
Note: Could you mention where you got/how you constructed your other attempt? Appscript is definitely not necessary, And it would be good to correct the record at the source.
CodePudding user response：
A solution was provided for this problem here by user I hope this is helpful to you