thomaspatton
01-29-2008, 08:14 AM
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
Got some help yesterday, which solved the issue here (http://vbaexpress.com/forum/showthread.php?t=17383), but then I ran into a different one. Basically, as you can see from the snippet above, everytime I would update a different row, it would change only row4.
So I rewrote mine as such :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then
Call MergePJtoGrad
End If
End Sub
Sub MergePJtoGrad()
Worksheets("Scores").Activate
c = 1
Do Until c = 2
With Me
If ActiveCell.Value = "" Then
With .Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.UnMerge
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End With
c = c + 1
ElseIf ActiveCell.Value = "x" Or ActiveCell.Value = "X" Then
Dim AdminDropDate As String
AdminDropDate = InputBox("Enter the date that Student was Admin Dropped.", "Enter Admin Drop Date")
With Me.Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
.Cells(1, 1).Value = "Admin Drop " & AdminDropDate
End With
c = c + 1
ElseIf ActiveCell.Value < 70 And ActiveCell.Value < 70 Then
Dim AcadDropDate As String
AcadDropDate = InputBox("Enter the date that Student was Acad dropped.", "Enter Acad Drop Date")
With Me.Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
.Cells(1, 1).Value = "Acad Drop " & AcadDropDate
End With
c = c + 1
Else
With Me.Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.UnMerge
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End With
c = c + 1
End If
End With
Loop
End Sub
I'm sure you advanced users can see the issue with that, but I can't seem to find a fix for the ActiveCell references.
First off, the active cell becomes the one you hit enter to, which means it's not calculating the scores correctly. Second, even IF it was hitting the correct activecell first, you HAVE to have Excel setup to jump-down upon data entry, or it will grab the wrong cells/data/and on and on.
What do I use to correct this ActiveCell junk?
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
Got some help yesterday, which solved the issue here (http://vbaexpress.com/forum/showthread.php?t=17383), but then I ran into a different one. Basically, as you can see from the snippet above, everytime I would update a different row, it would change only row4.
So I rewrote mine as such :
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 9 Then
Call MergePJtoGrad
End If
End Sub
Sub MergePJtoGrad()
Worksheets("Scores").Activate
c = 1
Do Until c = 2
With Me
If ActiveCell.Value = "" Then
With .Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.UnMerge
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End With
c = c + 1
ElseIf ActiveCell.Value = "x" Or ActiveCell.Value = "X" Then
Dim AdminDropDate As String
AdminDropDate = InputBox("Enter the date that Student was Admin Dropped.", "Enter Admin Drop Date")
With Me.Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
.Cells(1, 1).Value = "Admin Drop " & AdminDropDate
End With
c = c + 1
ElseIf ActiveCell.Value < 70 And ActiveCell.Value < 70 Then
Dim AcadDropDate As String
AcadDropDate = InputBox("Enter the date that Student was Acad dropped.", "Enter Acad Drop Date")
With Me.Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
.Cells(1, 1).Value = "Acad Drop " & AcadDropDate
End With
c = c + 1
Else
With Me.Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 12))
.Value = ""
.UnMerge
.Interior.ColorIndex = xlNone
.Font.ColorIndex = 1
End With
c = c + 1
End If
End With
Loop
End Sub
I'm sure you advanced users can see the issue with that, but I can't seem to find a fix for the ActiveCell references.
First off, the active cell becomes the one you hit enter to, which means it's not calculating the scores correctly. Second, even IF it was hitting the correct activecell first, you HAVE to have Excel setup to jump-down upon data entry, or it will grab the wrong cells/data/and on and on.
What do I use to correct this ActiveCell junk?