microsoft excelmicrosoft-excel-2007
Excel 2007 has added support for cell styles, which I find very useful. I love it when I can change formatting in one place and the change propagates throughout the spreadsheet.
I also love conditional formatting.
Can the two be combined? Can I conditionally apply a cell style?
You can fake it but it takes some setup. It will work unless you need really big shapes. Here are the steps for creating n different conditional format shapes in Excel 2010.
tl;dr Use 1 extra cell and 1 text box for every condition you want. Each text box is setup to show giant text with huge outlines and shadows, all the same color. The extra cells are setup to only show text if the value meets the condition.
Create n helper columns that will show blank unless the value meets the correct condition. A simple example for n = 3 cases might be: (we'll get into why I used "888" later
=IF(A1=1,"888","")
=IF(A1=2,"888","")
=IF(A1=3,"888","")
For now, though, just fill the helper columns with text, not a formula.
Format the text color in each helper column to be correct for that condition
No FillHere are some screenshots of a mockup I did.
Note that which box has color changes as the value changes. If I had stacked these up, it would look like a single shape changing color.
Here's a macro that creates a conditional format for each row in your selection. It does this by copying the format of the first row to EACH row in the selection (one by one, not altogether). Replace B1:P1 with the reference to the first row in your data table.
Sub NewCF()
Range("B1:P1").Copy
For Each r In Selection.Rows
r.PasteSpecial (xlPasteFormats)
Next r
Application.CutCopyMode = False
End Sub
To use, highlight the un-formatted rows in your dataset (in my case, B2:P300) and then run the macro. In the example below, note that the max numbers in the first two rows are 5 and 15, respectively; both cells are dark red.
I'm sure there's a faster solution than this, though.
Best Answer
You can't conditionally apply a style, but you can conditionally remove it. If you format the cell with the style that you ultimately want, then depending on the situation, you can then clear that style with the conditional formatting.