Rank

[Jock]

Once I have 'ranked' the rows by a 'priority' number, how can I automatically
get rows with priority 1 to move to the top of the sheet followed by priority
2 and so on?
Also, how can I get rid of the #N/A error (still in RANK) in cells reporting
on incomplete data?

 

[Mike H]

Jock,

You can avoid the NA error with:-

=IF(ISNA(RANK(A1,A$1:A$100)),"",RANK(A1,A$1:A$100))

When you've ranked numbers you can do a data sort to get then in the order
you want.

Mike

 

[Jock]

Thanks Mike. Error sorted, although it ranked in decending order. Fixed it
though.
Rather than manually sort, can I automate the sorting so that for example,
when I prioritise something to '1' it moves from row 25 (or whatever) to row
1 an soon as '1' is entered in A25?

 

[Mike H]

Jock,

I'm not aware of any worksheet function to do this but you could monitor A25
with the worksheet change event and if a 1 is enter call a macto:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$25" Then
If Target.Value = 1 Then
MsgBox ("You put a 1 in A25")
'< delete the msgbox bit and record
' yourself doing your sort and paste it here
End If
End If
End Sub

Mike

 

[Jock]

Got around it by having a button/macro.
I have been trying to code cell shading to red if priority '1', then orange
for "2" and finally green for '3'. I can't get it to work.
Any ideas?

 

[Mike H]

Jock,

I'd do that with conditional formatting but if you want a macro try:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target.Value
Case 1
icolour = 3
Case 2
icolour = 44
Case 3
icolour = 4
End Select
Target.Interior.ColorIndex = icolour
End If
End Sub

 

[Jock]

Great Mike, thanks.
I would like the entire row coloured though and I have tried to incorporate
this line:
Set myRow = Target.Offset(0, -2).Resize(, 5)
but it has no effect. Can you tell me where this would go?

Thanks again,

Jock

 

[Mike H]

It's not nearly that complicated, just change this line in the macro to get
the entire row shaded.

Target.EntireRow.Interior.ColorIndex = icolour

Mike

 

[Jock]

How easy is that!!
Great...however can it be stopped at, say column Z??
If it's not do-able, I can live with it.

Thanks for your help Mike, really appreciated

Jock

 

[Mike H]

Mike

This change to the macro shaeds up to column Z

Range(Target, Target.Offset(0, 25)).Interior.ColorIndex = icolour

Mike

 

[Jock]

Fixed.
Thank you Mike

Jock

Mike

This change to the macro shaeds up to column Z

Range(Target, Target.Offset(0, 25)).Interior.ColorIndex = icolour

Mike