variation of 2 rows

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

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 xx

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

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
>
>