If you want your users to answer your questions in a specific order, you can use both data validation and conditional formatting, as described in this tip.Īpplying Conditional Formatting to Multiple Worksheets It is not unusual to use Excel to gather the answers to users' questions. This tip examines different approaches you can take to getting the alert. As a due date approaches, you may want that fact drawn to your attention, in some fashion. If no one knows of a maximum, is there a programmatic way to determine the maximum? Preferably one that won't take 5 hours to run.You may use Excel to track due dates for a variety of purposes. My question is: Is there a maximum number of conditional formatting rules that can be applied in Excel? This a primarily academic pursuit (I highly doubt I'll ever need more than 5,000 conditional formatting rules), so please let me know if this question is not appropriate for the forum. Rather than continue to spot check large numbers randomly (and wait several hours for the results), I thought I'd go to the community with this one. It took approximately 6 seconds to write 1,000 rules, 3 minutes to write 5,000 rules, and about 23 minutes to write 10,000 rules.

It should be noted that the time it took to run the macro appeared to increase exponentially. However, anything more than 5,000 caused my file to crash when editing a cell.

I was able to write at least 10,000 individual rules for one cell. MsgBox 'There was an Error: ' & Chr(10) & Err.Number & ', ' & Err.Description, vbExclamation With (xlCellValue, Operator:=xlEqual, Formula1:=Text(i)) So, in order to find out if there was a maximum in Excel 2016, I wrote a macro to create thousands of conditional formatting rules: Sub CondForm() It seems in earlier versions, the maximum number of conditional formatting rules was 3.

In my research I did find references to Excel 2007 and earlier ( here and here), but no references to later versions of Excel. Inspired by this question, I went down a rabbit hole trying to discover the maximum allowable number of conditional formatting rules that can be applied to a cell.

Macro conditional formatting excel 2016