In Sheet X I have a bunch of cells that are conditionally formatted with a fill color. I want the colors from Sheet X to be dynamically updated to Sheet Y and Sheet Z. That is, if cell X:3:D is conditionally formatted to be red, I want cells Y:3:D and Z:3:D to be red as well. If I change the value in X:3:D to cause the cell to be blue, sheets Y and Z should to dynamically update.

  • 1
    Quite important: Do Y:3:D have the same value as X:3:D? – Envite Dec 2 '13 at 6:48
  • @Envite No, and that's the crux of the question. If they did, I could simply copy the conditional formatting rules over to the new sheet. Sorry if that wasn't clear. – Hooked Dec 2 '13 at 15:03

Are you willing to have the key values replicated onto the other sheets?  If so, set SheetY!Q3 to =SheetX!D3, and then have SheetY!D3 conditionally formatted using the value in SheetY!Q3 (but otherwise the same conditional formatting rules as SheetX!D3).  You can hide Column Q when you get it working.

  • P.S. It’s easier to communicate when you use standard conventions; e.g., Sheet1!D3 rather than X:3:D. – Scott Dec 3 '13 at 1:29
  • Thanks for the convention heads up, it's been awhile since I've used Excel. This solution won't work however, I want to place values in a new worksheet but have the colors carry over from a previous one. – Hooked Dec 3 '13 at 3:53
  • I don’t understand. What do you mean by “a previous” worksheet? A sheet in a different workbook? Yesterday’s values (which no longer exist in the file)? – Scott Dec 3 '13 at 16:26
  • Sorry "previous" was a bad choice of words. Given a workbook with three sheets, I want to update a cell in Sheet1!Q3 which colors it due to conditional formatting from the rules on Sheet1. I want that color to be copied to Sheet2!Q3 and Sheet3!Q3 but I still want to be able to edit the cell contents of Sheet2 and Sheet3. – Hooked Dec 3 '13 at 17:42
  • OK, so what’s unworkable about my answer? I’m suggesting that you set up areas on Sheet2 and Sheet3 -- maybe out of the way (e.g., Column Q, when everything else is in, say, Columns A-M) and/or hidden -- where you can automatically replicate the values from Sheet1 that are the basis for the conditional formatting. So cells in Sheet2 Column D would be conditionally formatted based on formulas referring to cells in Sheet2 Column Q (which are replicas of cells in Sheet1). – Scott Dec 3 '13 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.