[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VBA - Keeping a Conditional Formatting Rule in first position

Posted on 2011-10-24
6
Medium Priority
?
271 Views
Last Modified: 2012-05-12
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
Comment
Question by:AndresHernando
  • 5
6 Comments
 
LVL 11

Accepted Solution

by:
ScriptAddict earned 2000 total points
ID: 37018933
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
 

Author Comment

by:AndresHernando
ID: 37029822
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
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37033423
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37033454
Oh the Author of the code snip thought he had an issue in this block, but it was elsewhere.  
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37033494
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
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37033691
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question