Automatic Converting of Cells Into Range Notation and Conditional Formatting

Cross-posted at https://www.mrexcel.com/board/thread...tting.1213810/

I am conditionally formatting large numbers of cells using lists such as A1, A2, A3, B1, B2 & B3. These cells can often be shortened by using range notation - so the list of cells shown could be shortened to A1:B3.

My questions are:
(a) Will spreadsheets work quicker if they are given data that is specified as a range as opposed to individual lists of cells?
(b) Is there a way of processing lists of cells (such as my A1, A2, A3, B1, B2 and B3) so they are automatically converted into the range notation? (Does this exist?!)

Thank you for any comments - it is much appreciated!

a) I don't know for certain and I don't know of anybody that has conducted empirical tests. However, I know a little about how software is written. There are two parts to executing a formula. The first part is parsing the formula. It is faster to parse a range expression than a list of cells. But the difference is so tiny that you would never detect it unless you had maybe a million formulas. Maybe not even then. Once it is parsed to some canonical form (which I am assuming that Excel does, because all good optimized compilers do that) then the second part, the processing time, will be the same.

Bottom line is that (usually) you should write formulas in such a way that they are clear and understandable before you worry about optimizing for performance. An exception to this will be array formulas, which can take very long to execute if they are not carefully constructed.

b) No. You could probably write some custom VBA do this but you would basically be writing a parser and it is probably not worth the effort (see a).

I would like to see what your actual conditional formatting is, so we can talk in concrete terms about your particular file.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I have edited your post to add this because you have fewer than 10 posts. But please take the time to review our rules. There aren't many, and they are all important.

Like I said, I would like to see your actual conditional formatting rules. You can attach a file (see yellow banner at the top of the page), or at least show your conditional formatting rule(s).

14,400 is a solid number but still within reason.

Hi - I thought I would follow this up because I have noticed that Excel automatically converts any cells provided into ranges when conditional formatting via VBA. I note that when I tried to do this manually the conversion was not taking automatically taking place (hence I asked this question.)

This is brilliant news for me, as I have kept working with just the individual cells which has also meant the coding is much easier. (So thank you Jeff for your comments as they have proven to be most helpful!)

The only other issue that I came across is when variables are expressed as "ranges" (e.g. Dim rng As range) they are not allowed to store more than 255 characters. Fortunately, I have solved this now.

Apologies about my cross-posting. I hope I can still post on this board from time to time!