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.

Please Login or Register  to view this content.

This appears to work, I just reformatted a little:

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
Dim cel As Range, Rng As Range
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("H3:H" & LR)

    Range("A2:H" & LR).Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("F2"), _
        Order2:=xlDescending, Key3:=Range("G2"), Order3:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

    For Each cel In Rng
        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 Sub 


Here it is in your own sheet. Type anything in column C and the whole thing sorts and colors column H.

PHP Code: 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
Dim cel As Range, Rng As Range
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("H3:H" & LR)

    Range("A2:H" & LR).Sort Key1:=Range("H2"), Order1:=xlAscending, Key2:=Range("F2"), _
        Order2:=xlDescending, Key3:=Range("G2"), Order3:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

    For Each cel In Rng
        Select Case cel.Value
            Case vbNullString
                Range(Cells(cel.Row, "A"), Cells(cel.Row, "J")).Interior.ColorIndex = xlNone
            Case "Out of Stock"
                Range(Cells(cel.Row, "A"), Cells(cel.Row, "J")).Interior.ColorIndex = 6
            Case "None on Hand"
                Range(Cells(cel.Row, "A"), Cells(cel.Row, "J")).Interior.ColorIndex = 43
            Case Is < 0.75
                Range(Cells(cel.Row, "A"), Cells(cel.Row, "J")).Interior.ColorIndex = 3
            Case 0.75 To 1
                Range(Cells(cel.Row, "A"), Cells(cel.Row, "J")).Interior.ColorIndex = 45
            Case Is > 1
                Range(Cells(cel.Row, "A"), Cells(cel.Row, "J")).Interior.ColorIndex = 41
        End Select
    Next cel

End Sub 


==========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

Thanks that turned out to look like crap.

LOL Not your fault, my colors sucked

Yeah, I do stuff like that occasionally and I always use the soft pastel colors, very pleasant to read.

Cheers.

Ah, but why "work it out" when you can just look them up!?

http://www.mvps.org/dmcritchie/excel/colors.htm

My favorites are 17, 19, 20, 22, 33-40.