I have a workbook and I would like expenses totaled when I am done with
entering expenses on two seperate sheets. So far that works well. When I
enter a negative dollar amount, I have code that turns the line red and
black if the dollar amount is positive. Works OK. When I leave the two
sheets the Deactivate routine runs. When this runs, the font color is
always set to black. On my main sheet, I have some cells that have a red
background and white text. It seems that the Calc_New_Expenses routine runs
each time. How can I prevent the Calc_New_Expenses routine from running
when I select my first sheet? I don't want the text turned black.
Hope this is clear and if not, I'll try to explain better.
Thanks,
Gary
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Name = "Construction Expenses" Or Sh.Name = "Misc Expenses" Then
Calc_New_Expenses
End If
End Sub
Sub Calc_New_Expenses()
'
'
Worksheets("Amounts").Range("cash_to_gary").Value = _
WorksheetFunction.SumIf(Range("Paid_By"), "Gary",
Range("Paid_By").Offset(0, -2)) _
+ WorksheetFunction.SumIf(Range("Paid_By_Misc"), "Gary",
Range("Paid_By_Misc").Offset(0, -2)) + _
WorksheetFunction.SumIf(Range("Paid_By"), "Charge",
Range("Paid_By").Offset(0, -2)) _
+ WorksheetFunction.SumIf(Range("Paid_By_Misc"), "Charge",
Range("Paid_By_Misc").Offset(0, -2))
Worksheets("Amounts").Range("cash_to_dom").Value = _
WorksheetFunction.SumIf(Range("Paid_By"), "Dom",
Range("Paid_By").Offset(0, -2)) _
+ WorksheetFunction.SumIf(Range("Paid_By_Misc"), "Dom",
Range("Paid_By_Misc").Offset(0, -2))
'
' The following code turns the row red if the dollar amount is negative
' and black if the dollar amount is positive
'
Dim MyRange As Range, MyRows As Integer, ThisRow As Integer
With ActiveSheet
Set MyRange = .UsedRange
MyRows = MyRange.Rows.Count
For ThisRow = 1 To MyRows
If IsNumeric(.Cells(ThisRow, 3)) Then
If .Cells(ThisRow, 3) < 0 Then
.Range(.Cells(ThisRow, 1), Cells(ThisRow, 5)).Font.Color
= vbRed
Else
.Range(.Cells(ThisRow, 1), Cells(ThisRow, 5)).Font.Color
= vbBlack
End If
End If
Next ThisRow
End With
End Sub
Bookmarks