Forum Discussion
Applying a style from the style gallery using conditional formatting
Background: I have a project tracking sheet that summarizes various stages of progress, depending on project type. On each summary page, I have been displaying a visual based on colour for overdue, due today, due in 3 days, 7 days, etc. I have been doing this on each summary page using conditional formatting, but want to use one formatting set throughout the workbook.
1) I've created cell styles in the formatting gallery for each due date range.
2) I am very familiar with creating conditional formatting.
I do not know how to apply the cell style from the gallery based on the conditional formatting rules, as opposed to creating the information within the rule (ie - pull from the gallery instead of me setting every time).
For various reasons, not limited to my own sanity and the fact no one else can read code if I die, I don't want to do this in any coding model, but rather through conditional formatting. Am I out of luck or is this possible?
2 Replies
- Being able to apply defined cell styles in conditional formatting is a much sort after feature and would be a such a time saver but it's not supported.
I've been trying to find where we can submit feature requests (aside from within Excel help) but can't find anywhere. I would vote a thousand times for this feature as it would literally save me hours of time.
Microsoft Office team please add this feature. It would be hugely popular! mmcksmith No, conditional formatting is limited and you can only select from and use the interface supplied. That said, you could consider setting a few 'generic' rules with corresponding format and just add cells, rows, columns, areas, etc to the conditional formatting 'applies to' section. I'm not sure if it would help but you could also use defined names in addition to or instead of individual cell references in the 'applies to' section. So for example you can set up conditional formatting for 'overdue' to be red background with bold red font, etc... and applies to ConditionalRange and then define ConditionalRange in the Define Names section. The tricky part is to remember that the relative reference in the conditional formatting is based on the upper left corner of the applies to range. So you need to make sure you account for that either in the conditional formula also using the defined name or by the defined name always having the most upper left cell included.
