So I'm using data validations alot.

for better organization I create tab called setup and I put my list of possible options there

let's say I want to categorize my credit card expenses

so I'll put a possible categories list in the setup tab as following:

enter image description here

now in the other tab: credit card expenses, I'll set data validation rule as such: enter image description here

(*) I usually select longer list than the existing one so I can easily add more later and it will already be defined in the list (for this example I can add more in cells L8:L17)

Now, to make things more readable I like to add colors, So I want that the following color theme will be applied in my credit card expenses:

enter image description here

Now, as far as I know, if I want to set such color theme, for N categories,

I need to set N different conditional formatting rules stating:

if X=="supermarket" => color=yellow

if X=="entertainment" => color=purple



Now, as you can now see, if N is very big, this process gets annoying, especially if I have multiple tabs such as credit card expenses and I need to set those rules to each of them.. Also if I want to change those colors later, I'll need to update those rules everywhere I did so.

Also, cutting and pasting cells kinda ruins conditional formatting rules so I don't quite like them..

so my question is:

Can I simply color up the cells in my setup tab and somehow set the data validation rule to copy the color with it from the range list?

  • 1
    No such option with Conditional formatting, the way out is to make a script.
    – Oleg_S
    Oct 1, 2020 at 7:47
  • "so my question is:" It would have been useful to say this in the first few sentences.
    – Tedinoz
    Oct 1, 2020 at 8:25


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.