• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

Excel 2007, Character Limit of Conditional Formatting "Applies to" List

Does anyone know how long of a list of separate (noncontiguous) cells or ranges one can put in the "Applies to" list for Excel 2007 conditional formatting?  And do you know any workarounds -- programmatic or otherwise -- to get past the limit?
0
Yaroslavl
Asked:
Yaroslavl
  • 8
  • 5
1 Solution
 
redmondbCommented:
Hi, Yaroslavl.

I couldn't see a mention of a specific Conditional Formatting limitation, but in Excel 2007 there is a calculation limit of 2,048 selected ranges(http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx).

(Excel 2010 also mentions a limit of 2,147,483,648 cells noncontiguous cells that can be selected (http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx).

Regards,
Brian.      
0
 
YaroslavlAuthor Commented:
Thank you for your response, and sorry that it has taken me so long to comment.  I am definitely having issues creating a range or a conditional format for the following noncontiguous cells listed below.  Have you any suggestions?

F10:F13, F15:F17, F19:F21, F23:F26, F28:F33, F35:F37, F39:F41, F43:F44, F46:F47, F49:F50, F52:F54, F56:F59, F61:F63, F65:F69, F71:F73, F75:F79, F81:F83, F85:F87, F89:F91, F93:F93, F95:F97, F99:F101, F103:F104, F106:F108, F110:F112, F114:F116, F118:F121, F123:F124, F126:F127, F129:F130, F132:F134, F136:F136, F138:F141, F143:F145, F147:F151, F153:F156, F158:F161, F163:F165, F167:F169, F171:F173, F175:F177, F179:F181, F183:F185, F187:F189, F191:F192, F194:F196, F198:F200, F202:F206, F208:F209, F211:F212, F214:F217, F219:F221, F223:F225, F227:F229, F231:F232, F234:F236, F238:F240, F242:F243, F245:F247, F249:F252, F254:F256, F258:F259, F261:F263, F265:F266, F268:F270, F272:F274, F276:F279, F281:F283, F285:F287, F289:F291, F293:F294, F296:F298, F300:F302, F304:F306, F308:F310, F312:F314, F316:F317, F319:F321, F323:F324, F326:F330, F332:F343, F345:F350, F352:F353, F355:F357, F359:F360, F362:F363, F365:F369, F371:F372, F374:F376, F378:F380, F382:F384, F386:F388, F390:F391, F393:F395, F397:F398, F400:F402, F404:F406
0
 
redmondbCommented:
Yaroslavl,

OK, I see your problem! The underlying code works, but is it appropriate for your requirements?
Sub Big_Selection()

Range("F10:F13,F15:F17,F19:F21,F23:F26,F28:F33,F35:F37,F39:F41,F43:F44,F46:F47,F49:F50,F52:F54,F56:F59,F61:F63,F65:F69,F71:F73,F75:F79,F81:F83,F85:F87,F89:F91,F93:F93,F95:F97,F99:F101").Select
Application.Union(Selection, Range("F103:F104,F106:F108,F110:F112,F114:F116,F118:F121,F123:F124,F126:F127,F129:F130,F132:F134,F136:F136,F138:F141")).Select
Application.Union(Selection, Range("F143:F145,F147:F151,F153:F156,F158:F161,F163:F165,F167:F169,F171:F173,F175:F177,F179:F181,F183:F185,F187:F189")).Select
Application.Union(Selection, Range("F191:F192,F194:F196,F198:F200,F202:F206,F208:F209,F211:F212,F214:F217,F219:F221,F223:F225,F227:F229,F231:F232,F234:F236,F238:F240,F242:F243,F245:F247,F249:F252,F254:F256")).Select
Application.Union(Selection, Range("F258:F259,F261:F263,F265:F266,F268:F270,F272:F274,F276:F279,F281:F283,F285:F287,F289:F291,F293:F294,F296:F298,F300:F302,F304:F306,F308:F310,F312:F314,F316:F317,F319:F321")).Select
Application.Union(Selection, Range("F323:F324,F326:F330,F332:F343,F345:F350,F352:F353,F355:F357,F359:F360,F362:F363,F365:F369,F371:F372,F374:F376,F378:F380,F382:F384,F386:F388,F390:F391,F393:F395,F397:F398,F400:F402,F404:F406")).Select

End Sub

Open in new window

Regards,
Brian.
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
redmondbCommented:
.. or even simpler (but limited to 30 "groups" of ranges)...
 
Sub Big_Selection_II()
Application.Union(Range("F10:F13,F15:F17,F19:F21,F23:F26,F28:F33,F35:F37,F39:F41,F43:F44,F46:F47,F49:F50,F52:F54,F56:F59,F61:F63,F65:F69,F71:F73,F75:F79,F81:F83,F85:F87,F89:F91,F93:F93,F95:F97,F99:F101") _
, Range("F103:F104,F106:F108,F110:F112,F114:F116,F118:F121,F123:F124,F126:F127,F129:F130,F132:F134,F136:F136,F138:F141") _
, Range("F143:F145,F147:F151,F153:F156,F158:F161,F163:F165,F167:F169,F171:F173,F175:F177,F179:F181,F183:F185,F187:F189") _
, Range("F191:F192,F194:F196,F198:F200,F202:F206,F208:F209,F211:F212,F214:F217,F219:F221,F223:F225,F227:F229,F231:F232,F234:F236,F238:F240,F242:F243,F245:F247,F249:F252,F254:F256") _
, Range("F258:F259,F261:F263,F265:F266,F268:F270,F272:F274,F276:F279,F281:F283,F285:F287,F289:F291,F293:F294,F296:F298,F300:F302,F304:F306,F308:F310,F312:F314,F316:F317,F319:F321") _
, Range("F323:F324,F326:F330,F332:F343,F345:F350,F352:F353,F355:F357,F359:F360,F362:F363,F365:F369,F371:F372,F374:F376,F378:F380,F382:F384,F386:F388,F390:F391,F393:F395,F397:F398,F400:F402,F404:F406")).Select
End Sub

Open in new window

0
 
YaroslavlAuthor Commented:
Thank you Brian!!  This worked beautifully.  With this method I am able to build the selection list in an array, select it, and then assign the conditional format.  I very much appreciate your help!!

Something I discovered related to this is that when applying a conditional format programmatically, it appears that, even if using range objects in the code, the cell on which the format formula is based must be selected in the worksheet.  Otherwise the formula ends up being other than what the code specifies.  How can this piece of info be added to the knowledge base?
0
 
redmondbCommented:
Thanks, Yaroslavl.

I'm not sure that I correctly understand your finding. In the attached file (code below), the selected cell is neither in the range being formatted, nor that being tested. It works fine in Excel 2010. Which version are you using?
Option Explicit

Sub Apply_CondForm()
Dim xRange As Range

Cells(12, 3).Activate
Set xRange = Range("B2:B11")

With xRange
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(A2,2)=0"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65280
        .TintAndShade = 0
    End With
    
    .FormatConditions(1).StopIfTrue = False

End With

End Sub

Open in new window

Regards,
Brian.
0
 
redmondbCommented:
0
 
YaroslavlAuthor Commented:
Hello Brian,

I apologize for the long delay in responding and hope you will still get this.  Thank you for the code/workbook.  I tried your code, both on a new workbook on my PC (using Win 7/Office 2007) and in your workbook opened on my PC, and both failed (just as my own code did). I've attached the workbook, so hopefully you can see the results after I ran the code on my PC.  (Note that before running the code, I manually removed conditional formatting on the sheet.)  To see the issue, edit the rule and look at the formula.  When I do this in cell B2, the formula shown is =MOD(XFD1048568,2)=0.  Where it gets that bizarre cell reference I have no idea, but this is exactly what was happening in my code -- until I first selected the range to be formatted.

Thank you!

Robert

Just in case your opening the file in Excel 2010 somehow corrects the formula in the rule, I'm also attaching a screen shot.
CondForm-Error.jpg
CondForm-Test-B.xlsm
0
 
redmondbCommented:
Hi, Yaroslavl.

No bother, I normally keep an eye on closed questions for a week or so.

Your file showed the same formula as your picture. I ran the macro and the results look fine (please see attached).

I'm running 2010, so, in principle, it's possible that this is a 2007 issue, but I think it's highly unlikely.

How does my file look to you before running the macro? After it? Assuming you see your peculiar results, can you try it again but with no add-ins or Personal.whatever ?

Thanks,
Brian.
CondForm-Test-B-V2.xlsm
0
 
redmondbCommented:
Hi, Yaroslavl.

Colour me surprised - it apparently is a 2007 issue as I replicated your results using 2007. I'll do some digging and see if I can find it documented anywhere. (It's late now, so it'll be tomorrow before I get back to you.)

Regards,
Brian.
0
 
YaroslavlAuthor Commented:
Thank you!  I'll look forward to your reply!

Robert
0
 
redmondbCommented:
Yaroslavl,

OK, there seem to be a few issues around Conditional Formatting in Excel 2007. I didn't find a definitive documenting of your issue but some explanation is give in the last post here.

So, this does look like one of those rare situation where you have to select something before doing something to it. (Only not quite - it's enough to select the first cell.)

This seems to have been fixed in 2010, but for backward compatibility I'll be doing this fix in any future Conditional Formatting code.

Thanks for an interesting find!
Brian.


Regards,
Brian.

 
0
 
YaroslavlAuthor Commented:
Brian -

Very interesting!  Thank you for the additional info!

Robert
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now