PDA

View Full Version : VBA Cell Format based on In-Cell Formula Result



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.*

Bob Phillips
02-12-2008, 11:58 AM
That Offset is not offsetting fromm the cell being tested as I think that you think that it is, because the only qualifier prior to that is Activesheet.

I am struggling to understand exactly what you are trying to do, but I would have thought it best to create range names for the start points of the different areas, and work off of these.

thomaspatton
02-12-2008, 12:09 PM
Alrighty.

As you can see in the Scoring Sheet, LastName & St# are copied several times in different areas. All the areas correlate to each other, but changes arent made manually in the "Go/No-Go" section. That entire portion is updated automatically. What I was envisioning is IF a student scores are low enough (i.e. the upper portion which callulates the values for Go/No-Go section) it automatically makes the GO/No-Go column (av:az) say "NO". That works fine through formulas but the part thats not working is if (av:az) says "NO" I wanted it to merge (L:Q) in the Examination Score portion and color it and other goodies. It ain't workin, though, as you can also plainly see.

*edit* also, I should add that a similar situation was fixed here (http://vbaexpress.com/forum/showthread.php?t=17405) for a different workbook and I tried using that particular piece of code with different cells and it doesn't work since the updating only happens when something is typed in that particular row. Only problem is that the "Go/No-Go" tracker doesn't get typed in at all. It all autoupdates based on the uppermost score sheet.

thomaspatton
02-12-2008, 12:40 PM
Hey XLD, if it's not too late, don't worry about this. I'm just going to use conditional formatting to achieve my result.