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

What I really want is to use the Pcent value (ie: 5%) to make th
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 x

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

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

Hi, thank you for your reply, but I cant get it to work :(
Can I send you a copy of the file for you to look at ?
Amy x

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

Hi, please disregard my last email, I have figured it out - I shoul
have been entering .5, not 0.05 thank you anyay !!

Amy xxxxxxx

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

Hi,
It should be 0.05 if you did mean 5%; 0.5 is 50%.

"AmyTaylor" wrote:


Hi, please disregard my last email, I have figured it out - I should
have been entering .5, not 0.05 thank you anyay !!

Amy xxxxxxxx


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