
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
Bookmarks