Change color depending on cell value

[Gary Paris]

I would like to Loop through my worksheet and if the value in Column 5 is
negative, I would like to color all 5 cells in that particular row red also.

I don't want to do this in conditional formatting, but I would like to know
how to "brute force" do this in code.

Thanks,

Gary

 

[JE McGimpsey]

one way:

Dim rCell As Range
For Each rCell In Range(Cells(1, 5), Cells(Rows.Count, 5).End(xlUp))
With rCell
If IsNumeric(.Value) Then _
If .Value < 0 Then _
.Offset(0, -4).Resize(1, 5).Interior.ColorIndex = 3
End With

 

[Guest]

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 .Cells(ThisRow, 5) < 0 Then _
.Range(.Cells(ThisRow, 1), Cells(ThisRow, 5)).Interior.Color = vbRed
Next ThisRow
End With

 

[Gary Paris]

Thanks for the code. It works when I tested it. One problem that I forgot
to mention. I have two worksheets that I would like to run this code
against.

I have the following routine

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

If Sh.Name = "Construction Expenses" Or Sh.Name = "Misc Expenses" Then
Calc_New_Expenses
End If

and I would like to run your code for each sheet. How can I run this code
in the Calc_New_Expenses routine and have it applied to each sheet?

Here is the contents of the routine as it stands now:

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))

Dim rCell As Range
For Each rCell In Range(Cells(1, 5), Cells(Rows.Count, 5).End(xlUp))
With rCell
If IsNumeric(.Value) Then _
If .Value < 0 Then _
.Offset(0, -4).Resize(1, 5).Interior.ColorIndex = 3
End With
Next

Exit Sub

Hope this makes sense.

Thanks,

Gary

 

[Gary Paris]

Thanks for the code. This works perfect!

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 .Cells(ThisRow, 5) < 0 Then _
.Range(.Cells(ThisRow, 1), Cells(ThisRow, 5)).Interior.Color = vbRed
Next ThisRow
End With

 

[Gary Paris]

One more thing. I did change the code to turn the text in rows with
positive values black. One problem is that there are other values that
change on different sheets. The text was white, now it's black. How can I
stop this from happening?

Thanks,

Gary

 

[Guest]

Probably the "ActiveSheet" was set to the other sheets at the time the code
ran? In any event, the bbest way to avoid it is to actually specify the
sheet name instead of using ActiveSheet; e.g. With Sheets("Sheet1")...

 

[Gary Paris]

My routine is called by the following routine

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

I tried placing the With Sheets("Sheet Name") instead of the Active Sheet
line you gave me, but it generated an error.

I also changed the following lines:

If .Cells(ThisRow, 5) < 0 Then _
.Range(.Cells(ThisRow, 1), Cells(ThisRow, 5)).Interior.Color =
vbRed
Next ThisRow

to
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


So when the main sheet is activated, the Font Color being black changes the
other cells that I have calculations for.

Maybe is there a way to turn off the Font Color? Or is there a way to just
run the routine if the two sheets I want to process are active?

Thanks,

Gary