Change color depending on cell value

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

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

In article <O9WMA#[email protected]>,
"Gary Paris" <[email protected]> wrote:

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

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" wrote:

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

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

"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> In article <O9WMA#[email protected]>,
> "Gary Paris" <[email protected]> wrote:
>
>> 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 for the code. This works perfect!


"K Dales" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>>
>>

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

"Gary Paris" <[email protected]> wrote in message
news:%[email protected]...
> Thanks for the code. This works perfect!
>
>
> "K Dales" <[email protected]> wrote in message
> news:[email protected]...
>> 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" wrote:
>>
>>> 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
>>>
>>>
>>>
>
>

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" wrote:

> 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
>
> "Gary Paris" <[email protected]> wrote in message
> news:%[email protected]...
> > Thanks for the code. This works perfect!
> >
> >
> > "K Dales" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" wrote:
> >>
> >>> 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
> >>>
> >>>
> >>>
> >
> >
>
>
>

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


"K Dales" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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
>>
>> "Gary Paris" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Thanks for the code. This works perfect!
>> >
>> >
>> > "K Dales" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" wrote:
>> >>
>> >>> 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
>> >>>
>> >>>
>> >>>
>> >
>> >
>>
>>
>>