Solved: Auto-Macro based on Cell Change in Column

[thomaspatton]

Moderate User
Excel 2003

I did some searching and googling and thought I had come up with some solutions to this issue... but, it taint werkin mayn!

Here's my code :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = i Then
MsgBox "Finally Working!", vbOKOnly
Application.Run ("MergePJtoGrad")
End If
End Sub
Sub MergePJtoGrad()
Dim CurCell As String
If Range("i4").Value = "" Then
Range("j4:u4").Select
ActiveCell.Value = ""
Selection.UnMerge
With Selection.Interior
.ColorIndex = xlNone
End With
Selection.Font.ColorIndex = 1
ElseIf Range("i4").Value = "x" Or Range("i4").Value = "X" Then
Dim AdminDropDate As String
AdminDropDate = InputBox("Enter the date that Student was Admin Dropped.", "Enter Admin Drop Date")
Range("j4:u4").Select
Selection.MERGE
With Selection.Interior
.ColorIndex = 3
End With
Selection.Font.ColorIndex = 6
ActiveCell.Value = "Admin Drop " & AdminDropDate
ElseIf Range("h4").Value < 70 And Range("i4").Value < 70 Then
Dim AcadDropDate As String
AcadDropDate = InputBox("Enter the date that Student was Acad dropped.", "Enter Acad Drop Date")
Range("j4:u4").Select
Selection.MERGE
With Selection.Interior
.ColorIndex = 3
End With
Selection.Font.ColorIndex = 6
ActiveCell.Value = "Acad Drop " & AcadDropDate
Else
Range("j4:u4").Select
ActiveCell.Value = ""
Selection.UnMerge
With Selection.Interior
.ColorIndex = xlNone
End With
Selection.Font.ColorIndex = 1
End If
End Sub


Macro only needs to run on Scores Sheet.
When I manually call the macro from the button object, it works fine.
Issue I'm having is this:

Based on the Worksheet_Change at the top, I want it to read when I change column "I" and run the MergePJtoGrad macro. I've tried "If Target.Column=9" also, and nothing. If column H is under 70 or has an x in it and then I enter column an X or <70 score in column I, it's supposed to run the macro to merge the rest fo the cells. However, it does nothing. Not even a "yer a dummy" error... so I don't know what I'm doing wrong here.

If this topic has been covered in depth and I'm just not searching for the right thing, link to post please. Otherwise, please tell me where the method to my madness is going awry!

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then
MsgBox "Finally Working!", vbOKOnly
Call MergePJtoGrad
End If
End Sub

Sub MergePJtoGrad()
Dim CurCell As String
With Me

If .Range("i4").Value = "" Then

With .Range("j4:u4")
.Value = ""
.UnMerge
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End With
ElseIf UCase(Range("i4").Value) = "x" Then

Dim AdminDropDate As String
AdminDropDate = InputBox("Enter the date that Student was Admin Dropped.", "Enter Admin Drop Date")
With .Range("j4:u4")
.Value = ""
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
.Cells(1, 1).Value = "Admin Drop " & AdminDropDate
End With
ElseIf Range("h4").Value < 70 And Range("i4").Value < 70 Then

Dim AcadDropDate As String
AcadDropDate = InputBox("Enter the date that Student was Acad dropped.", "Enter Acad Drop Date")
With .Range("j4:u4")
.Value = ""
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
.Cells(1, 1).Value = "Acad Drop " & AcadDropDate
End With
Else

With .Range("j4:u4")
.Value = ""
.UnMerge
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End With
End If
End With
End Sub

[thomaspatton]

I would love to get payed to do what you do and know the things you do...

It would also help to mention that I was a tard and hadd my code inside of a Module instead of the actual sheet...

I almost replied with "It ain't werkin!", due to that "slight" oversight >.>