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. 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 In Week 1 you will learn all about advanced data management strategies in Excel. Interior.Color = RGB(Int(255 * Rnd), Int(255 * Rnd), Int(255 * Rnd)) Video created by for the course 'Everyday Excel, Part 2'. 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. You can select one column, several columns or the entire table if you want to apply your conditional format to rows. 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. To set up a conditional formatting rule based on a formula in Excel 2019, Excel 2016, Excel 2013 and Excel 2010, carry out these steps: Select the cells you want to format.