I am preparing a excel dashboard where in I need to change different colours based on other cell colours across various worksheets.
Say for example. I have a worksheet named "dashboard" with a row header "A". I have another worksheet named "1" which has 5 rows namely a,b,c,d,e respectively. a is filled with red colour b is filled with green c with Yellow d with orange using conditional formatting.
I need to change the colour of dashboard row to the max colour found in sheet "1" which is RED/Orange/Amber/green in the order of precedence.
Can I use a formula to do this conditional formatting or do I need to use a vba script. Pls. help