VBA Macros using Formulas

[intrinsically.extragalactic]

I have over a gig of spreadsheets that I need to digest immediately.
Step 1 is making it all easily visible.

What I want to do is write several Subs that can format cells dependent
up the cell value. Thanks to boards like this, I understand how to do
this.

What I am using now looks something like this:
----------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("AD353:AD802")) Is Nothing Then
Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = 0: .Interior.ColorIndex = 2
.Font.ColorIndex = 2
Case 0.01 To 0.5: .Interior.ColorIndex = 6
Case 0.51 To 0.979: .Interior.ColorIndex = 4
Case 0.98 To 1.02: .Interior.ColorIndex = 41
Case Is > 1.02: .Interior.ColorIndex = 7
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub
-----------------------------------------------------

This works well if all the cells have values.

***My problem is that I some of my columns that need to be formatted
(font and cell color) are defined as formula results [e.g. cell AD353
is "=IF(Q353=0,0,ROUND(R353/Q353,3))"]. I can't really get rid of these
formulas.

How do I make a macro that references a formula result? Is this
possible?

I've tried various foolishness such as using [Case
"=IF(Q353=0,0,ROUND(R353/Q353,3))" = 0: .Interior.ColorIndex = 2] and
that seemed to be an error.

 

[JE McGimpsey]

***My problem is that I some of my columns that need to be formatted
(font and cell color) are defined as formula results [e.g. cell AD353
is "=IF(Q353=0,0,ROUND(R353/Q353,3))"]. I can't really get rid of these
formulas.

How do I make a macro that references a formula result? Is this
possible?

I've tried various foolishness such as using [Case
"=IF(Q353=0,0,ROUND(R353/Q353,3))" = 0: .Interior.ColorIndex = 2] and
that seemed to be an error.

I'd suggest you use the Worksheet_Calculate event in addition to the
Worksheet_Change event. Perhaps something like:


Private Sub Worksheet_Calculate()
Dim rCell As Range
On Error GoTo CleanUp
For Each rCell In Range("AD353:AD802").SpecialCells( _
xlCellTypeFormulas, xlNumbers)
With rCell
Select Case .Value
Case Is = 0
.Interior.ColorIndex = 2
.Font.ColorIndex = 2
Case Is <= 0.5
.Interior.ColorIndex = 6
Case Is < 0.98
.Interior.ColorIndex = 4
Case Is <= 1.02
.Interior.ColorIndex = 41
Case Else
.Interior.ColorIndex = 7
End Select
End With
Next rCell
CleanUp:
End Sub

You could actually use this alone if making an entry in the specified
range causes a calculation, say a sum, or a follow-on calc, in the sheet
(in which case you'd leave off the .SpecialCells(...) portion).

Note that once a value becomes zero, the font color changes to white,
but you don't have it changing back at any point. If that's *not* what
you want, I'd consider adding

.Font.ColorIndex = xlColorIndexAutomatic

right after the

With rCell

line.

Note also that I eliminated the gaps in your ranges (e.g., what should
happen if the result is 0.005, 0.503, etc.?), which your
ROUND(R353/Q353,3) may generate. You may not have any problem values in
your manual entries, but with calculations, small rounding errors can
sometimes lead to invalid results, even in values that look correct. For
instance, a calculation which should result as 0.51 may be formatted so
that it displays as 0.51, but when fully expanded to XL's 15 digits of
precision, may actually be

0.509999999999998

which your Select Case wouldn't pick up. Rounding, as you do, would
eliminate that, but your formula was given as an example, so I wasn't
sure it was the only type.