Highlight MAX in each contiguous range.

bigmyk2k

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Why not use Pivot table to get the min and max values in your dataset?

Why not use Pivot table to get the min and max values in your dataset?

Overall Min and Max would be pretty easy, I'm looking for Max values for each and every discrete period.

For instance, I'm working with about 2 years worth of rain data. In every year, there are 2 6-Month periods, 12 1-Month periods, 365 24-Hour periods, etc. However, for every one of those, there are 35,040 rolling average periods (4 15-min periods per hour, 24 hours per day, 365 days per year).

My cell formula only displays a value above a certain threshold, so there are ranges of varying length that show a result, and ranges of varying length that don't. What I need to find is the local maximum in every contiguous range of cells that show a result.

That is no answer to my question.

Why not use Pivot table to get the min and max values in your dataset?

That is no answer to my question.

Why not use Pivot table to get the min and max values in your dataset?

The data is not tabulated in such a way as to facilitate this. It has not contiguous rows, and several cross references.

Is it possible for you to re-arange the data, so a pivot table (a very powerfull tool in excel) can be used.

It can be done (depend on the lay out of the data) with VBA.

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.
I am not 100% certain about your data as it relates to the formulas, but assuming you are interested in only finding the maximums of contiguous ranges of constants (ignoring the formulas), then this might work for you...
Code:
``````Sub HighlightMaximumsInContiguousColumnRanges()
Dim C As Long, LastCol As Long, Arr As Range
LastCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior.ColorIndex = 6
For C = 1 To LastCol
For Each Arr In Columns(C).SpecialCells(xlConstants).Areas
Arr.Replace WorksheetFunction.Max(Arr), "", xlWhole, , , , False, True
Next
Next
Application.ReplaceFormat.Clear
End Sub``````

It can possibly be done, but it isn't my data, and the format is ideal for most of what we currently use it for.
This would be a little taste, but assume that every cell outside of the first two rows has a formula in it.
Data - RG 3

 * A B D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB 11 Time Rainfall (IN) 30-MIN 1-HR 2-HR 3-HR 6-HR XXX XXX 24-HR 48-HR 72-HR 5-DAY 10-DAY XXX 15-MIN 30-MIN 1-HR 2-HR 3-HR 6-HR XXX 18-HR 24-HR XXX 72-HR 5-DAY 12 XXX 0.00 * * * * XXX * * * * XXX XXX XXX * - - - - - XXX XXX - XXX - XXX - 13 03/27/2014 1:30 PM 0.00 XXX * * * * XXX * * * XXX * * XXX - - - - - XXX - - - - - - 14 XXX XXX 0.00 * * * XXX * * XXX * * * * XXX - - XXX - - XXX - XXX - - - - 15 03/27/2014 2:00 PM 0.00 0.00 0.00 * * * * * XXX * * * * XXX - XXX XXX - - XXX - XXX - XXX - XXX 16 03/27/2014 2:15 PM 0.00 XXX 0.00 * XXX * * * * * * * * * - - - XXX - XXX - - XXX - - - 17 03/27/2014 2:30 PM 0.00 0.00 0.00 * * * * * * * * * * * XXX - XXX - - - - XXX - - - - 18 XXX XXX 0.00 0.00 * * * * * * * * XXX XXX XXX - - - - - - - XXX XXX - - -

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Last edited:
Hey Rick, thanks for taking a look. I'm not great with VBA, can you give me an idea of what that is going to do?

The columns I'm looking in are R through AB, would that have an effect on what I enter?
Also, as stated elsewhere, I don't want to touch or rearrange the data, just call out/highlight the local maxima.
I am actually pretty close to getting this with a highlight cells rule, but it highlights the cell before (above) the one I'm looking for, and sometimes skips a highlight altogether.

Last edited:
Hey Rick, thanks for taking a look. I'm not great with VBA, can you give me an idea of what that is going to do?
It is going to make the background color of each cell containing a maximum value within its own column-wise contiguous region yellow. No data is touched or move.

The columns I'm looking in are R through AB, would that have an effect on what I enter?
I have to change the code so it looks in the correct columns. Here is the revised macro...
Code:
``````Sub HighlightMaximumsInContiguousColumnRanges()
Dim C As Long, Arr As Range
Application.ReplaceFormat.Clear
Application.ReplaceFormat.Interior.ColorIndex = 6
For C = 18 To 28
For Each Arr In Columns(C).SpecialCells(xlConstants).Areas
Arr.Replace WorksheetFunction.Max(Arr), "", xlWhole, , , , False, True
Next
Next
Application.ReplaceFormat.Clear
End Sub``````

Replies
3
Views
583
Replies
8
Views
2K
Replies
1
Views
354
Replies
5
Views
634
Replies
3
Views
236

1,196,057
Messages
6,013,165
Members
441,751
Latest member
336448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back