The fourth conditional format

[Barney]

In the following worksheet, I would like to highlite the 4th ranking cell in
the "Today Rank" column and 6,5,4,3,2,1 "Tie-breaker" cells in the same row.
Since my Excel 2002 only allows three conditional formats is there a way to
do it with Visual Basic? Can someone who know how, please send the code?


Scores Team Today Today Prior Season Tie-breakers
In Best 7 Rank Pts Points 6 5 4 3 2 1
8 Deercreek 262 2 1643 1905 228 194 158 120 82 42
8 Eagle Harbor 233 14 1757 1990 206 179 150 116 80 42
8 Hidden Hills 261 4 1733 1994 229 194 157 119 80 40
8 Jax G & CC 257 9 1849 2106 225 193 158 120 81 42
8 Magnolia Point 259 5 1779 2038 226 192 156 120 82 42
8 Marsh Creek 227 15 1786 2013 197 166 135 104 72 38
8 Marsh Landing 266 1 1774 2040 230 194 158 121 83 42
8 Orange Park 262 2 1756 2018 230 196 159 122 84 44
8 Osprey Cove 237 12 1706 1943 208 177 144 111 77 41
8 Ponte Vedra 259 5 1805 2064 229 196 163 127 88 45
8 Queen's Harbour 253 10 1744 1997 218 183 147 111 75 38
8 San Jose 258 8 1803 2061 228 192 156 119 82 44
8 Sawgrass 235 13 1814 2049 209 179 147 111 75 39
8 St Johns 259 5 1780 2039 232 199 164 126 86 44
8 World Golf 243 11 1784 2027 212 179 146 113 77 40


Thanks in advance,

Barney

 

[IanC]

If I understand correctly, you want to find the 4th smallest number in the
"Today Rank" column and alter it's format to highlight it. You also want to
format the 6,5,4,3,2,1 columns on the same row.

If that's the case, you only need 1 condition set.

Assuming your data starts at A1, the first data row will be 3 and Today Rank
will be column D.

Set conditional formatting on D3 to Formula is and input the formula as
=RANK($D3,$D$3:$D$17,1)=4

Copy the cell the Paste Special > Formats to anywhere you need the
conditional formatting to apply. In your example this would be D317 and
G3:L17

 

[Barney]

No, I want to highlite the first four values in Todays Rank and in the
6,5,4,3,2,1 columns. As you can see, I already have the first three values
highlited.

Thanks,

Barney

 

[IanC]

No, I want to highlite the first four values in Todays Rank and in the
6,5,4,3,2,1 columns. As you can see, I already have the first three
values highlited.

I couldn't see that from the information you gave, but I understand now.

If you'd be happy with highlighting the 4 rankings the same then change the
condition formula to

=RANK($D3,$D$3:$D$17,1)<=4

Otherwise you need to use code.

 

[Don Guillett]

I already sent this to you in a workbook that does it for you.

Sub colorem()
For i = 4 To 18
Select Case Cells(i, "J")
Case 1: x = 3: y = 2
Case 2: x = 5: y = 2
Case 3: x = 10: y = 2
Case 4: x = 4: y = 0
Case Else: x = 0: y = 0
End Select
Cells(i, "j").Interior.ColorIndex = x
Cells(i, "m").Resize(, 6).Interior.ColorIndex = x
Cells(i, "j").Font.ColorIndex = y
Cells(i, "m").Resize(, 6).Font.ColorIndex = y
Next i
'I did the loops two different ways so you can see alternates
For Each c In Range("L4:l18")
'MsgBox Application.Rank(c, Range("l4:l18"))
Select Case Application.Rank(c, Range("l4:l18"))
Case 1: x = 3: y = 2
Case 2: x = 5: y = 2
Case 3: x = 10: y = 2
Case 4: x = 4: y = 0
Case Else: x = 0: y = 0
End Select
c.Interior.ColorIndex = x
c.Font.ColorIndex = y
Next c
End Sub

 

[Barney]

Don,

There was nothing attached in either email that you sent.

Thanks,

Barney

 

[Barney]

Don,

I pasted the below code into my sheet code and nothing happened. No cells
are highlighted at all.

Barney

 

[Don Guillett]

Yes there was!!

 

[Don Guillett]

It is a SEPARATE macro assigned to a button or shape. NOT a worksheet change
macro.
Why can't you get attachments.