Highlight MAX in each contiguous range.

[bigmyk2k]

I am working in a spreadsheet analyzing rainfall data. This sheet has 50,000+ rows, and ~35 columns.

About half of the columns are formulaic, and are conditionally formatted to highlight based on Min>>Max.

As you travel down a column, you may come across anywhere from 3-300 contiguous cells that are displaying a result, while the IF function in the rest has left them blank.

Currently, every cell in all 50,000+ rows of each column is highlighting on the same scale. Is there anyway to automatically parse the highlight so that each contiguous group of cells in each column has only it's own Max highlighted?

Certainly, this is possible manually, but that is completely absurd to think of doing.

 

[bigmyk2k]

Hey Rick, that's really close. It ran and highlighted all of the contents of Row 10, which I didn't paste above. It doesn't seem to be recognizing the percentage values resulting from the formulae in all of the lower rows.

 

[Rick Rothstein]

Hey Rick, that's really close. It ran and highlighted all of the contents of Row 10, which I didn't paste above. It doesn't seem to be recognizing the percentage values resulting from the formulae in all of the lower rows.

The problem is I don't fully understand the layout of your data completely, so I do not know how to code for it. Is there anyway you can post a before and after worksheet (not picture of a worksheet) of what you want using a smallish sample of real data and formulas (maybe a few dozen rows or so) to onenote, boxdrive, or some other such posting facility (such a free file-sharing website)? This would be so much easier if we could see real original values and formulas, and then see which ones you want highlighted.

 

[bigmyk2k]

The problem is I don't fully understand the layout of your data completely, so I do not know how to code for it. Is there anyway you can post a before and after worksheet (not picture of a worksheet) of what you want using a smallish sample of real data and formulas (maybe a few dozen rows or so) to onenote, boxdrive, or some other such posting facility (such a free file-sharing website)? This would be so much easier if we could see real original values and formulas, and then see which ones you want highlighted.

Click to open the notebook in OneNote:
onenote:https://d.docs.live.net/d0c626150288d979/Public/Highlight Example/

Click to open the notebook in Web browser:
https://onedrive.live.com/edit.aspx/Public/Highlight Example?cid=d0c626150288d979&id=documents?&<o></o>

 

[Rick Rothstein]

Click to open the notebook in OneNote:
onenote:https://d.docs.live.net/d0c626150288d979/Public/Highlight Example/

Click to open the notebook in Web browser:
https://onedrive.live.com/edit.aspx/Public/Highlight Example?cid=d0c626150288d979&id=documents?&<o></o>

I don't see any of the formulas you alluded to in the worksheet? Also, you saved from a large row number, so whatever you were referring to when you said "It ran and highlighted all of the contents of Row 10"... I cannot see that either. If OneNote is not holding the formulas, then why don't you send the file directly to me (please address the two comments I just made with what ever you send me)... I'll the account open for a short time to allow your message in. My email address is...

rick DOT news AT verizon DOT net

I have to step out for a few hours, so don't expect a fast reply from me. Oh, and please mention this thread's title in your email so I can more easily find my way back to this thread.

 

[bigmyk2k]

Thanks for your help, file sent.

EDIT:
I don't know what your mail server is doing, but it is kicking it back saying it is too big. Specifically, it is saying that my 16MB message exceeds the 33MB size limit.

 

[Rick Rothstein]

Thanks for your help, file sent.

EDIT:
I don't know what your mail server is doing, but it is kicking it back saying it is too big. Specifically, it is saying that my 16MB message exceeds the 33MB size limit.

Try Zipping it... that usually compresses thing enough to make them email-able.

 

[bigmyk2k]

Try Zipping it... that usually compresses thing enough to make them email-able.

That is zipped. Initially it kicked back my 23MB unzipped file, saying it was 36MB.

 

[Rick Rothstein]

That is zipped. Initially it kicked back my 23MB unzipped file, saying it was 36MB.

Hmm! Try lopping off a large slug of rows from the bottom (so that Row 10 is still in the file... I don't need every row in your file, just enough to show the constants and formulas so I can get a sense of the layout.