calculation based on row and column values

[AmyTaylor]

I have the above vba, which loops thru rows b9 to 383 and checks the cell values, colouring the cells in G depending upon the result.
My question is this = at the moment this is only usable for checking B against BF. What I would like is for it to go from bF to CJ and check each row value against row 385 for each row.
Thanks for help anyone.



Sub TrafficLights()
Dim R As Integer
Dim Pcent As Integer
Pcent = 0.05
For R = 9 To 383 ' note the number range relates to the rows
If Range("BF" & R).Value < (Range("bf385").Value + Pcent) Then
Range("bf" & R).Interior.Color = vbGreen
Else
Range("bf" & R).Interior.Color = vbRed
End If
Next R
End Sub

End Sub

[Trevor Shuttleworth]

AmyTaylor

you could try something like this:

Sub TrafficLights2()
Dim iRow As Integer
Dim iColumn As Integer
Dim Pcent As Integer
Pcent = 0.05
Application.ScreenUpdating = False
For iColumn = 58 To 88 ' number range relates to columns BF to CJ
For iRow = 9 To 383 ' note the number range relates to the rows
If Cells(iRow, iColumn).Value _
< Cells(385, iColumn).Value + Pcent Then
Cells(iRow, iColumn).Interior.Color = vbGreen
Else
Cells(iRow, iColumn).Interior.Color = vbRed
End If
Next iRow
Next iColumn
Application.ScreenUpdating = True
End Sub

But I think you'd be far better using Conditional Formatting.

Regards

Trevor


"AmyTaylor" <[email protected]> wrote
in message news:[email protected]...
>
> I have the above vba, which loops thru rows b9 to 383 and checks the
> cell values, colouring the cells in G depending upon the result.
> My question is this = at the moment this is only usable for checking B
> against BF. What I would like is for it to go from bF to CJ and check
> each row value against row 385 for each row.
> Thanks for help anyone.
>
>
>
> Sub TrafficLights()
> Dim R As Integer
> Dim Pcent As Integer
> Pcent = 0.05
> For R = 9 To 383 ' note the number range relates to the rows
> If Range("BF" & R).Value < (Range("bf385").Value + Pcent) Then
> Range("bf" & R).Interior.Color = vbGreen
> Else
> Range("bf" & R).Interior.Color = vbRed
> End If
> Next R
> End Sub
>
> End Sub
>
>
> --
> AmyTaylor
> ------------------------------------------------------------------------
> AmyTaylor's Profile:
> http://www.excelforum.com/member.php...o&userid=20970
> View this thread: http://www.excelforum.com/showthread...hreadid=388411
>