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.

 

[Oeldere]

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

 

[bigmyk2k]

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.

 

[Oeldere]

That is no answer to my question.

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

 

[bigmyk2k]

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.

 

[Oeldere]

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.

 

[Rick Rothstein]

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

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

 

[bigmyk2k]

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

 

[bigmyk2k]

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.

 

[Rick Rothstein]

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

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