calculation based on row and column values

[AmyTaylor]

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

 

[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