I tried it but here is the error it returned:
Run-time error '91':
Object variable or With block vriable not set

I clicked Debug and this is the line it highlighted:
If Target = Intersect(Target, Range("H3:H" & Cells(Rows.Count,
8).End(xlUp).Row)) Then



"JLGWhiz" wrote:

This modifies your current worksheet change code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("e3:e2100")) Is Nothing Then
With Target(1, -2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
If Target = Intersect(Target, Range("H3:H" & Cells(Rows.Count,
8).End(xlUp).Row) Then
If UCase(Range("H7")) = "YES" Then
Sheets("Old in").Range("F7").Copy
Sheets("Redeployment").Range("A4")
ElseIf UCase(Range("H7")) = "NO" Then
Sheets("Old in").Range("F7").Copy Sheets("Disposal").Range("A4")
End If
End If

End Sub

I did not test it, so give it a test run before permanently deleting the old
code. You cannot have two separate worksheet_change macros on the same
sheet, so you will neet to comment out (put apostrophes in front of code
lines) the old code while you test the new one.




"jcurless" wrote:

Okay, First I want to say I am new to this so you'll have to bare with me...
I am using Excel 2003

What I have:
Inventory tracking Work book with 5 work sheets. (Old in, redeployment,
disposal, device info, and location) the last 2 are tables used only for
vlookups.
The main worksheet is €œold in€� and has 6 columns
(b,c,e,f,g,h) (Others are hidden for later use if needed)
In Column H the user types yes or no to answer a question:
"is the device redeployable"

What I need:
If the text in work sheet €œold in€� cell H7 = YES
I need to copy the text listed in work sheet €œold in€� cell F7 to work sheet
€œRedeployment€� cell A4.
If the text in work sheet €œold in€� cell H7 = NO
I need to copy the text listed in work sheet €œold in€� cell F7 to work sheet
€œDisposal€� cell A4.
I want this to happen every time a cell in Column H is updated.
I currently have code that will auto populate the date in column B when
Column E is updated. I would need to ensure that the code doesnt effect that
action. Here is the code I have for that...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Not Intersect(Target, Range("e3:e2100")) Is Nothing Then
With Target(1, -2)
.Value = Date
.EntireColumn.AutoFit
End With
End If
End Sub

I have posted this on other sites but no one can figure out how to help!
If anyone would like to try to tacle this with me please do so!!
I can send a copy of the file if that would help!