calculation based on row and column values
 

I have the above vba, which loops thru rows b9 to 383 and checks th
cell values, colouring the cells in G depending upon the result.
My question is this = at the moment this is only usable for checking
against BF. What I would like is for it to go from bF to CJ and chec
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 Su

--
AmyTaylo
-----------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...fo&userid=2097
View this thread: http://www.excelforum.com/showthread.php?threadid=38841

calculation based on row and column values
 

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" wrote
in message ...

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