Solved: Conditional Format

[zoom38]

I have about 25 ranges on a worksheet that I want to highlight the highest value in each range. Conditional formatting only allows for 3 ranges. How can I do it in vba? Is there a rank function in vba? I cant find it in vba help.

Thanks
Gary

[austenr]

Try this from DRJ's KBase entry:

[VBA]
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error Goto 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "Tom", "Joe", "Paul"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "Smith", "Jones"
Cell.Interior.ColorIndex = 4
Cell.Font.Bold = True
Case 1, 3, 7, 9
Cell.Interior.ColorIndex = 5
Cell.Font.Bold = True
Case 10 To 25
Cell.Interior.ColorIndex = 6
Cell.Font.Bold = True
Case 26 To 99
Cell.Interior.ColorIndex = 7
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub
[/VBA]

[lenze]

I have about 25 ranges on a worksheet that I want to highlight the highest value in each range. Conditional formatting only allows for 3 ranges. How can I do it in vba? Is there a rank function in vba? I cant find it in vba help.

Thanks
Gary

Am I missing something? If you want to highlight the highest value in each range, can't you CF each range separately? Besides, CF allows you only 3 Conditions, not 3 ranges. So if you have your ranges named, say rgn1,rgn2,etc., then select rgn1 and CF Formula is =MAX(rgn1). Repeat for each range, but maybe I'm not understanding you.

lenze

[zoom38]

Lenze you are correct, I was mistaken. Not having that much experience with conditional formatting and reading over the help I assumed you could only use it 3 times(ranges) per worksheet, not realizing it can only be used for 3 conditions many times(ranges).

Thanks