Hi,

Try this: note pcent should be DIMed as DOUBLE not INTEGER

Sub TrafficLights()
Dim R As Integer
Dim Pcent As Double
Pcent = 0.05
For R = 9 To 20 ' note the number range
' Greater than 5% less
If Range("A" & R).Value * (1 + Pcent) < Range("J" & R).Value Then
Range("G" & R).Interior.Color = vbGreen
Else
' greater than 5% more
If Range("A" & R).Value Range("J" & R).Value * (1 + Pcent) Then
Range("G" & R).Interior.Color = vbRed
End If
End If
Next R
End Sub

HTH

"AmyTaylor" wrote:


I have the following code, which looks at the cells BF9, BF10 etc and if
the corresponding cell J9, J10 etc is greater then cell G9, G10 etc
changes colour.

What I really want is to use the Pcent value (ie: 5%) to make the
calculation a bit more complicated, whereby the value has to be 5%
greater or 5% lower to trigger a change in the colours.

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

I hope this makes sense, and hope someone can help me
Thank you
Amy xx


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=388380