Hi Everyone,
I tried to search the forum before posting this questions but I couldn't quite figure it out based on some of the responses to different situations. Here is my situation:
I want to create a Macro that automatically "moves" or "copy/paste/insert" rows based on some criteria. We are managing a sales list and if a user qualifies a deal they insert a "Y" into the "L" column corresponding to the row, no action is taken. If they insert a "N" the row automatically hides via a macro and a subtotal calculation is in place @ C:60. In A:62, a conditional format is in place to "alert" the user that they must move names up. The magic number is 35. If they fall below they are required to move rows up by inserting copied cells from the data set below.

Is there a way I can automate that last process? So if the user falls below "35" a macro could automatically move rows? If this isn't possible, I was also thinking of just creating a pop-up message that prevents the user from closing the docuement until they have 35?

Thanks for any suggestions/input!


Auto Hide Row MACRO:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("J3:J600")) Is Nothing Then
Select Case UCase(Target)
Case Is = "N"
Target.EntireRow.Hidden = True
Case Is = "Y"
Target.EntireRow.Hidden = False
End Select
End If

End Sub

Formulas=
in Cell C:60 =SUBTOTAL(103,C2:C58)---- (This counts the "active names")

Conditional Format in A:62 =IF(C60<35,"Please move Customers up from the bottom to maintain an active 35","No Action Required")