thomaspatton
02-12-2008, 11:50 AM
I've scratching my head here because everytime I think I have something down, I finc out even without errors... crap just doesn't work the way you want it to sometimes...
Anyway, I ripped off some code from XLD in another thread (http://www.vbaexpress.com/forum/showthread.php?t=17150&highlight=update+cells) that seemed to be having a similar problem.
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1
If .Cells(i, "A").Value = "N" Then .Rows(i).Delete
Next i
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
And modified it a bit to look like this :
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
LastRow = .Cells(.Rows.Count, "AV").End(xlDown).Row
For i = LastRow To 1 Step 1
If .Cells(i, "AV").Value = "NO" Then
With .Offset(62, -31)
.Value = "Not Eligible"
With .Resize(, 6)
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
End With
End With
End If
Next i
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
And this is what I thought it would do.
Within the "Scoring" Sheet, it would keep an eye on the "Re-Entry Qualified" portion of the "Go/No-Go" Tracker. Then (using the .Offset functions) it would select certain cells within the "Examination Scores" tracker a bit further down the page and either reformat them to default or, if the No-Go column says "NO", it'll merge and color and do some magic stuff.
*edit* Also, lemme add I don't necessarily need to be typing in any particular place on the Sheet. I just wanted to set it up so it would recognize anytime the value changed to "NO".
But, I'm not even getting a merge out of it, much less unicorns and fairies.
Where did I go wrong?
*And don't start with the merged cells thing >< Formatting is not MY option and comes from someone much higher than me who knows the difference between merged/unmerged cells but not the benefits and drawbacks of each.*
Anyway, I ripped off some code from XLD in another thread (http://www.vbaexpress.com/forum/showthread.php?t=17150&highlight=update+cells) that seemed to be having a similar problem.
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1
If .Cells(i, "A").Value = "N" Then .Rows(i).Delete
Next i
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
And modified it a bit to look like this :
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
LastRow = .Cells(.Rows.Count, "AV").End(xlDown).Row
For i = LastRow To 1 Step 1
If .Cells(i, "AV").Value = "NO" Then
With .Offset(62, -31)
.Value = "Not Eligible"
With .Resize(, 6)
.Merge
.Interior.ColorIndex = 3
.Font.ColorIndex = 6
End With
End With
End If
Next i
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
And this is what I thought it would do.
Within the "Scoring" Sheet, it would keep an eye on the "Re-Entry Qualified" portion of the "Go/No-Go" Tracker. Then (using the .Offset functions) it would select certain cells within the "Examination Scores" tracker a bit further down the page and either reformat them to default or, if the No-Go column says "NO", it'll merge and color and do some magic stuff.
*edit* Also, lemme add I don't necessarily need to be typing in any particular place on the Sheet. I just wanted to set it up so it would recognize anytime the value changed to "NO".
But, I'm not even getting a merge out of it, much less unicorns and fairies.
Where did I go wrong?
*And don't start with the merged cells thing >< Formatting is not MY option and comes from someone much higher than me who knows the difference between merged/unmerged cells but not the benefits and drawbacks of each.*