Results 1 to 13 of 13

VB Script Sorting and Conditional Formatting?

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-17-2009
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2013/2016
    Posts
    107

    VB Script Sorting and Conditional Formatting?



    Excel 2003

    Ok so I've been working on this "HUGE" Inventory spreadsheet.

    What I have is a Validation List in C1.

    So I asked the Sheet to check and every time anything is updated in Column 3 to Sort and perform conditional formatting.

    These to functions work when I run together in one Macro. Except the Check Column 3 is not there.

    Anyway, here's the code. I suspect that my Target.Column function is wrong, but I don't know how to fix it.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Check that cell changed was in col C. If not, exit.
    If Target.Column = 3 Then
        
        Range("A2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Range("A2:J65536").Select
        Selection.Sort Key1:=Range("H3"), Order1:=xlAscending, Key2:=Range("F3") _
            , Order2:=xlDescending, Key3:=Range("G3"), Order3:=xlDescending, Header _
            :=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
            , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
            xlSortNormal
        Range("C1").Select
    
        Dim cel As Range
        Dim FormatRange As Range
    
        Set FormatRange = Range("H3:H65536")
        
        For Each cel In FormatRange
        Select Case cel.Value
        Case vbNullString
        cel.Interior.ColorIndex = xlNone
        Case "Out of Stock"
        cel.Interior.ColorIndex = 6
        Case "None on Hand"
        cel.Interior.ColorIndex = 43
        Case Is < 0.75
        cel.Interior.ColorIndex = 3
        Case 0.75 To 1
        cel.Interior.ColorIndex = 45
        Case Is > 1
        cel.Interior.ColorIndex = 41
        End Select
        Next cel
    
    End If
    End Sub
    Last edited by fasterthanyours; 07-29-2009 at 09:18 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1