• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

Excel VBA - Keeping a Conditional Formatting Rule in first position

Conditional Formatting screenshot
For some reason, one of my conditional formatting rules will go from being the top rule (which is what I want), to going to the bottom is a row is deleted.

How can I keep the rule on top or put it back on top?

 Conditional Formatting screenshot
0
AndresHernando
Asked:
AndresHernando
  • 5
1 Solution
 
ScriptAddictCommented:
I think you'll notice on the right side of delete rule some arrows.  Select the rule you want to move and then click on the up arrow until you have it on top where u want it.

-SA
0
 
AndresHernandoAuthor Commented:
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.
0
 
ScriptAddictCommented:
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

Open 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 

Open in new window


can't see the formula's but that is one way to do it.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
ScriptAddictCommented:
Oh the Author of the code snip thought he had an issue in this block, but it was elsewhere.  
0
 
ScriptAddictCommented:
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

Open in new window

0
 
ScriptAddictCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now