The Aim: The image shows the “key” on the left, where cells will be coloured manually using mouse input. A (red background) B (green background) C (blue background) On the right, you see the "data" where content-matching cells should be formatted to match their key (as I've already done for representation purpose in the image)
Initial Situation: No cells are coloured or formatted in any way. The Excel spreadsheet has column A with certain values, And rest of the columns (C onwards) have the same/different values in a random manner (some cells even empty). Not all values in "key" area will be found in "Data" area or vice versa. No new data is being added in any area. User will only colour certain Values in "Key" area as per their wish.
Thus all “C” cells in the Data area should be coloured blue when "C" in Key area is coloured blue. Furthermore, if I change the formatting of “C” in the Key to have a purple background, all the “C” cells should switch from blue to purple. Also, if I add more to the Key (say, “D” with a yellow background) then any “D” cells should become yellow; if I remove a Key entry, then matching values in the Data area should revert to default styling.
I'm open to different trigger techniques,like manually running a macros via shortcut etc. I suspect that if any of this is possible it will require VBA, but I’ve never used it so I’ve no idea where to start if that’s the case. The closest answer to my question was found here but doesn't entirely work for me: