Beyond 3 conditional formats

  • Thread starter Thread starter Barney
  • Start date Start date
B

Barney

In a column of numbers the largest amoung them should have a red
background, 2nd largest blue, third yellow, and fourth green. Conditional
formatting only provides for three conditions. Is there a way to make it
work with more than three conditions?

Thanks,

Barney
 
Here is an example
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"
Dim cell As Range
Dim aryColours

aryColours = Array(36, 38, 34, 5, 10, 6, 7, 3, 4)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Me.Range(WS_RANGE)
If cell.Value = "" Then
cell.Interior.ColorIndex = xlColorIndexNone
Else
cell.Interior.ColorIndex = aryColours( _
Application.Match(cell.Value, Me.Range(WS_RANGE), 0))
End If
Next cell
End If


ws_exit:
Application.EnableEvents = True
End Sub





'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Bob,

I'm sorry. What is this? A macro or? Where do I put this code? Is
there something I can read?

Barney
 
The foot of my post told you exactly where to place it.

It is an example, you will need to change it to your conditions.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Back
Top