Originally Posted by
xld
Sorry I don't know your name so I can't address you properly, but here goes.
I picked up your idea of a range and whilst it will work, I do not think it is flexible. If you want to add a cell to the range you have to update the name, a non-trivial task.
What I did was to pick up on another idea that you had (at least I think this was what you were suggesting). I created a style called myStyle and applied it to cell A1. The great thing about that is if you copy cell A1 and paste it to any other cell, the CF goes with that copy, SO DOES THE STYLE. So when I run CFUpdate, the user nominates the source range, picks up the current CF colour, and also the style name. The user also picks the replacement colour. It then goes through all the cells in the usedrange on every sheet to see if the cell has that style, if so it updates the CF colour (don't forget the source cell will have that style, so it too gets updated).
Admittedly, I have to check every cell, whereas with a named range you wouldn't, but the flexibility of just copying A1 to another cell rather than updating the name justifies this IMO. Also, don't forget that named ranges cannot span worksheets, style usage can.
Also, no worksheet/workbook event is triggered by a CF change, which is why I drive it from a button in this example.
I think this is a great utility, not so much for the coding, that is pretty straightforward, but its concept. Some of that was my idea, but the original idea was yours. And I attrribute the way that I am usingh styles to you, if if you didn't say/think that, I read it that way, so you generated the idea. The ability to extend dependent CF cells so easily, to update the formula and have them all reflect that update, to change the colour, great stuff.
Of course, it needs some polishing. What if we have 2/3 conditions, but the basis is there.