Thanks Jim.

The changes you made allowed for the sort to happen. FYI, the cells in G
depend only on other cells in the same worksheet (Sheet 1).

I've been trying to make the macro hide Column C in Sheet 2 (which was the
numerical basis for the sort) after sorting. I tried placing the code within
the With and also outside of it (before End If). If I wanted to make that
happen and also adjust the sort (eg use custom list) and delete rows that
don't have a value in Column C, where would I place that code (all changes
are for Sheet 2)?

Thanks again.

"Jim Thomlinson" wrote:

Assuming that the cells in G depend only on other cells in the same worksheet
then...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler

If Not Intersect(Target, Range("G3:G110").Precedents) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False

With Sheets("Sheet2")
.Range("A1:C50").Sort Key1:=.Range("C2"), Order1:=xlDescending, _
Header:=xlYes
End With

End If
ErrorHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

If there are off sheet precidents then things get ugly.
--
HTH...

Jim Thomlinson


"oms" wrote:

I am trying to get excel to automatically run a macro based off a change in
any cell in a column. I've been trying worksheet_change but have not had
much luck.

Objective:
If there is a change to any of the cells in column G of Sheet 1 (cell
contains a formula that works off other cells), I want the macro to sort data
on Sheet 2 (info that I linked from Sheet 1). I am unhiding and hiding a
column in Sheet 2 because it contains the value that I'm sorting by but I
don't want it to show in the final product.

I've attached the code below. Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G3:G110")) Is Nothing Then

Application.EnableEvents = False
Application.ScreenUpdating = False
On Error Resume Next

Sheets("Sheet 2").Select

'Unhide Column C
Columns("B:D").Select
Selection.EntireColumn.Hidden = False

'Sort based on Column C value
Range("C2").Select
Range("A1:C50").Sort Key1:=Range("C2"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Hide Column C
Columns("C:C").Select
Selection.EntireColumn.Hidden = True

End If

Application.EnableEvents = True
Application.ScreenUpdating = True
On Error GoTo 0

Sheets("Sheet 1").Select

End Sub