Multi column conditional formatting

[Ricki]

I don't know if this is possible but here goes.

I have a speadsheet which I need to find the top 5 items based on item in column A (Lower # More important) and a value B (Higher # more important) and highlight them.

for example:
COLUMN A COLUMN B
1 5
2 4
3 7
2 6
1 7
In this example say I want the top item most important based on column A (which would be 1) and 7 in column B.

Is there a way to do this? I would like the value in Column B to be highlighted.

 

[Logit]

Paste the following into a regular module :

Sub LargestInRange_array()
  Dim sh As Worksheet, arr, nrR As Long, i As Long
 
  Set sh = ActiveSheet             'use here the sheet you need
  arr = sh.Range("B2:B15").Value 'put the range in an array
 
  nrR = 1 'the number of Top to be returned (that 10 to 30, in your question)

  'clear the previous returned Top:
  'sh.Range("B2:B" & sh.Range("B" & sh.Rows.Count).End(xlUp).Row).ClearContents
  For i = 1 To nrR
    sh.Range("C" & i + 1).Value = WorksheetFunction.Large(arr, i)
  Next i
 
  arr = sh.Range("A2:A15").Value
  For i = 1 To nrR
    sh.Range("D" & i + 1).Value = WorksheetFunction.Small(arr, i)
  Next i
End Sub

The results should be displayed in C2 and D2

 

[kvsrinivasamurthy]

Try. Select B2:B6.
Formula for CF.

=AND($A2=MIN($A$2:$A$6),$B2=AGGREGATE(14,6,$B$2:$B$6/($A$2:$A$6=MIN($A$2:$A$6)),1))

 

[kvsrinivasamurthy]

Try. If Only B column is to be CF ,select B2:B6. IF Full range is to be CF then select A2:B6
Formula for CF.

=AND($A2=MIN($A$2:$A$6),$B2=AGGREGATE(14,6,$B$2:$B$6/($A$2:$A$6=MIN($A$2:$A$6)),1))

 

[Peter_SSs]

I need to find the top 5 items

In your sample there are only 5 rows so wouldn't all 5 values in column B be highlighted?