Maximum number of conditional formatting rules in Excel 2016?

Question

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. I have never heard of such a limit. In my research I did find references to Excel 2007 and earlier (here and here), but no references to later versions of Excel. It seems in earlier versions, the maximum number of conditional formatting rules was 3.

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()

On Error GoTo ErrHandle

Application.ScreenUpdating = False

Dim Upper As Integer
Upper = 8000

Dim Start As Double
Start = Timer

Dim Rng As Range
Set Rng = Range("A1")

Dim Text() As String
ReDim Text(1 To Upper)

Rng.FormatConditions.Delete

    For i = 1 To Upper
        Text(i) = "Text" & i
        With Rng.FormatConditions.Add(xlCellValue, Operator:=xlEqual, Formula1:=Text(i))
            .Interior.Color = RGB(Int(255 * Rnd), Int(255 * Rnd), Int(255 * Rnd))
        End With
    Next i

ExitHandle:
    Application.ScreenUpdating = True
    Debug.Print Upper & ", " & Timer - Start
    Exit Sub

ErrHandle:
    MsgBox "There was an Error: " & Chr(10) & Err.Number & ", " & Err.Description, vbExclamation
    Resume ExitHandle

End Sub

I was able to write at least 10,000 individual rules for one cell. However, anything more than 5,000 caused my file to crash when editing a cell. It should be noted that the time it took to run the macro appeared to increase exponentially. 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.

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. 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.

My question is: Is there a maximum number of conditional formatting rules that can be applied in Excel?

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...

Thanks for the consideration!

the big change to conditional formatting was in Excel 2007 where the “unlimited” conditions were introduced. Excel 2003 had a limit of 3 conditions — barry houdini, Oct 29 '17 at 12:22
Then for Excel 2007 and later, the only limitation to the amount of conditional formatting rules is the amount that one's computer can handle? (In my case ~5000) — E. Merckx, Oct 30 '17 at 14:40
For future users: "Number of conditions on a range is no longer limited by number." See [here](https://msdn.microsoft.com/en-us/library/bb286672(v=office.11).aspx). — E. Merckx, Nov 09 '17 at 19:31
This question should really be closed as it's not programming related. I understand that there is code in this question, but the code is not the subject of the question, just the tool to test how many conditions can be applied. This is a question about formatting cells in Excel. It's not about programming. — Enigmativity, Nov 29 '17 at 21:52
@E.Merckx - Pop it on https://superuser.com/. That's what that site is for. — Enigmativity, Nov 29 '17 at 21:57

score 1 · Answer 1 · edited May 06 '20 at 23:34

Using Excel 2013, I have found that if you have a lot (hundreds) of conditional formatting rules addressing a mix of individual cells and cell ranges, then when you try to copy and paste commands it causes the Excel to freeze on the spinning donut and the title bar shows "Not responding". You then have to use Task Manager kill Excel followed by the usual document recovery. I verified this by clearing out all conditional formatting rules and copy and paste of both single and cell ranges works as expected. So while there may not be a stated limit, there certainly is an operational limit on the number of conditional formatting rules before Excel freezes up - very annoying!!!

Maximum number of conditional formatting rules in Excel 2016?

1 Answers1