[oldbarnes]

If this is confusing I can email a short example of what I am referring to.

1.
Cells D56 through I56 individually pull totals from a different tab, respectively for their column headings using a formula in them....=IF(ISNA(VLOOKUP(D13,'TB by Approp'!$A$1:$L$1998,9,FALSE)),"0.00",VLOOKUP(D13, 'TB by Approp'!$A$1:$L$1998,9,FALSE)).

Cells D56 through I56 are Sum'd in J56 with excel's sum feature. K56 is blank in case I would like to add dollar adjustments. J56 and K56 are added together in L56.

2.
Cell N56 plus O56 are Sum'd in P56 with excel's sum feature. O56 is blank in case I would like to add dollar adjustments. N56 uses the same feature as the very top formula.

L56 - P56 = R56 (Variance or difference).

3.
In T56 the following formula is applied to compute the percent difference.
=IF(ISERROR(R56/L56*100), "0.00",R56/L56*100)
In T56, the above formula is also used to get rid of the known #DIV/0!. When dividing, the numerator is larger than 0 and the denominator is 0 = #DIV/0!.


4.
I am currently using a vba script, pasted into a worksheet, from the ozgrid.com website pasted below: modified to use 7 different possiblities.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("t56:t97")) Is Nothing Then
Select Case Target
Case 0
icolor = 2
Case -1 To -0.01
icolor = 4
Case 0.01 To 1
icolor = 4
Case -5 To -1.01
icolor = 44
Case Is < -5
icolor = 3
Case 1.01 To 5
icolor = 44
Case Is 5
icolor = 3
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub


Currently, when using this script I add any adjustment I need to in cells K56 and O56. Cell T56 computes a numeric percentage but does not change color. If I select T56, push F2 and then hit enter cell T56 color script will be applied and the cell will change color.

5. My question is that I would like to place a color in the adjacent cell (U56) as T56 is modified and have cell U56 automatically change color....without having to select the cell, push F2 and then hit enter to apply any color coding.


Also the following VBA Script, pasted into an individual module, is used in conjuction with the formula in Cell T56. this is found on www.mrexcel.com posted by Escalus

Sub Remove_Formula_Errors()
Dim rng As Range, cell As Range, fmla As String
Set rng = Cells.SpecialCells(xlCellTypeFormulas, 16)
For Each cell In rng
fmla = Right(cell.Formula, Len(cell.Formula) - 1)
cell.Formula = "=if(iserror(" & fmla & "), """"," & fmla & ")"
Next
End Sub



Any thoughts?