variation of 2 rows

[AmyTaylor]

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

 

[Toppers]

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]

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

 

[AmyTaylor]

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

 

[Toppers]

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