Excel VBA - Keeping a Conditional Formatting Rule in first position

ScriptAddict, thanks, but I need for it to happen automatically upon a row being deleted by a user.  
I was hoping for a simple way to control the order of conditional formatting entries, but I'll just write code to apply the pertinent conditional formatting to the named range every time a row is deleted.

Sorry I assumed you were using the GUI window.  

for 2003 something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
	If Not Intersect(Target, Range("A1:A10")) is Nothing Then
		Select Case Target
			Case 1 To 5
				icolor = 6
			Case 6 To 10
				icolor = 12
			Case 11 To 15
				icolor = 7
			Case 16 To 20
				icolor = 53
			Case 21 To 25
				icolor = 15
			Case 26 To 30
				icolor = 42
			Case Else
			'Whatever
		End Select
		Target.Interior.ColorIndex = icolor
	End If
End Sub

Select allOpen in new window

For 2010 try this:

Sub CondtionalFormats() 
     'Ranges - actual workbook has 10+, this is a sample of 2.
    Set GoodAvail = Range("$Q$3") 
    Set BadAvail = Range("$Q$5") 
     
    Dim mavail As Range 
    Dim yavail As Range 
     
     'Colours done this way to ensure am using Excel 2003 standard colours even if opened in 2010.
    ThisWorkbook.Colors(2) = RGB(255, 255, 255) 'White
    ThisWorkbook.Colors(10) = RGB(0, 128, 0) 'Green
    ThisWorkbook.Colors(40) = RGB(255, 153, 0) 'Orange
    ThisWorkbook.Colors(1) = RGB(0, 0, 0) 'Black
    ThisWorkbook.Colors(3) = RGB(255, 0, 0) 'Red
     
     'This is the first "If, ElseIf, Else, End" that starts endless looping - it's preceeded by 6 previous that work correctly, and they look identical to me.
     'Availability Monthly Conditional Format
    For Each mavail In Range("Q13:Q631").Cells 
         'Colour it White
        If mavail.Offset(0, -16).Value = "" Then 
            mavail.Interior.ColorIndex = 2 
            mavail.NumberFormat = "0%; " 
             'Colour it Green with hidden text
        ElseIf mavail.Value >= GoodAvail And mavail.Value = "1" Then 
            mavail.Interior.ColorIndex = 10 
            mavail.NumberFormat = ";;; " 
             'Colour it Green
        ElseIf mavail.Value >= GoodAvail Then 
            mavail.Interior.ColorIndex = 10 
            mavail.NumberFormat = "0%; " 
             'Colour it Orange
        ElseIf mavail.Value > BadAvail.Value And mavail.Value < GoodAvail.Value Then 
            mavail.Interior.ColorIndex = 40 
            mavail.NumberFormat = "0%; " 
             'Colour it Red
        ElseIf mavail.Value <= BadAvail.Value Then 
            mavail.Interior.ColorIndex = 3 
            mavail.NumberFormat = "0%; " 
             'Colour it White
        Else 
            mavail.Interior.ColorIndex = 2 
            mavail.NumberFormat = "0%; " 
        End If 
    Next mavail 
     
     'Availability Yearly
    For Each yavail In Range("AJ13:AJ631").Cells 
         'Colour it White
        If yavail.Offset(0, -35).Value = "" Then 
            yavail.Interior.ColorIndex = 2 
            yavail.NumberFormat = "0%; " 
             'Colour it Green with hidden text
        ElseIf yavail.Value >= GoodAvail And yavail.Value = "1" Then 
            yavail.Interior.ColorIndex = 10 
            yavail.NumberFormat = ";;; " 
             'Colour it Green
        ElseIf yavail.Value >= GoodAvail Then 
            yavail.Interior.ColorIndex = 10 
            yavail.NumberFormat = "0%; " 
             'Colour it Orange
        ElseIf yavail.Value > BadAvail.Value And yavail.Value < GoodAvail.Value Then 
            yavail.Interior.ColorIndex = 40 
            yavail.NumberFormat = "0%; " 
             'Colour it Red
        ElseIf yavail.Value <= BadAvail.Value Then 
            yavail.Interior.ColorIndex = 3 
            yavail.NumberFormat = "0%; " 
             'Colour it White
        Else 
            yavail.Interior.ColorIndex = 2 
            yavail.NumberFormat = "0%; " 
        End If 
    Next yavail 

Select allOpen in new window

can't see the formula's but that is one way to do it.

Oh the Author of the code snip thought he had an issue in this block, but it was elsewhere.  

One final block

If you need help let me know what the formula's are and I'll get to work :)

With Range("D2:D" & lastrow)
    .Select
    .FormatConditions.Delete               'remove this line if you wish
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR(D2<>0;D2<>"""");A2<>123;A2<>987;A2<>852;A2<>753;A2<>""User"")"
    .FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
    .FormatConditions(1).Interior.ColorIndex = 36
    .FormatConditions(1).Interior.TintAndShade = 0
End With

Select allOpen in new window

MSDN - Objects
Main two for this project:
Conditial Format Collection Ref
Conditional Formating Object Item

Other Useful Conditional Formating Related Links:
CellFormat Properties/Methods
Color Format
Overriding Conditional Formating
Format Color Object