highlighting minimum and maximum values

[koala]

Hi forum,

Can someone help me with this problem.

I have 20 columns ( B to U )
I have 46 rows ( 2 to 47 )

Each cell has data in it.

I want to highlight the minimum value (blue) and the maximum value (red) for each column.

The data changes daily so it needs to be dynamic. (maybe an 'event change' type macro)

I also thought I might be able to use "conditional formatting" but that required me to place the min/max value in another cell and then refer to it. Also conditional formatting has only 3 occurances and these are sometimes used for other purposes.

Any assistance is gratefully appreciated

cheers
Koala

[Bob Phillips]

Use conditional formatting with formulae of

=B1=MAX(B:B)

and

=B1=MIN(B:B)

By selecting columns B:U

[JONvdHeyden]

How are the numbers captured? Are they plugged in manually or are they formulated?

If you have exhausted your Conditional Formatting allowance and need a VBA solution, then perhaps this if the figures are result of formula:


Private Sub Worksheet_Calculate()
Dim rCell As Range
For Each rCell In Range("B2:U47")
rCell.Interior.ColorIndex = 0
If rCell.Value = WorksheetFunction.Min(Cells(2, rCell.Column).Resize(46, 1)) Then
rCell.Interior.ColorIndex = 5
End If
If rCell.Value = WorksheetFunction.Max(Cells(2, rCell.Column).Resize(46, 1)) Then
rCell.Interior.ColorIndex = 3
End If
Next rCell
End Sub

or if they are plugged in manually:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2:U47")) Is Nothing Then Exit Sub
Dim rCell As Range
For Each rCell In Range("B2:U47")
rCell.Interior.ColorIndex = 0
If rCell.Value = WorksheetFunction.Min(Cells(2, rCell.Column).Resize(46, 1)) Then
rCell.Interior.ColorIndex = 5
End If
If rCell.Value = WorksheetFunction.Max(Cells(2, rCell.Column).Resize(46, 1)) Then
rCell.Interior.ColorIndex = 3
End If
Next rCell
End Sub


To install this:
Copy the relevant procedure
Right click your sheet tab that this should apply to > View Code
Paste this into the code pane

[koala]

Thanks xld


That gives me the result I needed on a dummy worksheet, however on my actual worksheet I have already used up the three spaces for conditional formatting

That aside, I just learnt a whole new area of excel because I didnt know you could use a formula in conditional formatting ... i was of the understanding you had to reference it to another cell that held the result of the formula.


cheers
Koala

[koala]

JONvdHeyden,

They are formulated ... they total up numerous other cells

Thanks for the code ... it works great

cheers
koala

[mdmackillop]

A slight tweak to Jon's code (welcome to VBAX Jon), to avoid calculating Min and Max for every cell in the range.

Option Explicit
Private Sub Worksheet_Calculate()
Dim rCell As Range, cel As Range
Dim x As Single, y As Single
Dim Rng As Range
Set Rng = Range("B2:U47")
Rng.Interior.ColorIndex = 0
For Each rCell In Rng.Columns
x = WorksheetFunction.Min(rCell)
y = WorksheetFunction.Max(rCell)
For Each cel In rCell.Cells
If cel = x Then cel.Interior.ColorIndex = 5
If cel = y Then cel.Interior.ColorIndex = 3
Next
Next rCell
End Sub

[JONvdHeyden]

Nice tweak and thanks for the welcome. Although I've been lurking here for a while now. :)