referencing a column

[AmyTaylor]

Hello, hope you can help here, trying to impress the boss by getting
this to work before he does xx

The following loops down rows 9 to 384, but I need it to run the same
code for coluns BF to CJ too - is that possible ?
The code only works for BF at the moment.....

Sub TrafficLights()
Dim R As Integer
Dim Pcent As Double
Pcent = 0.5
For R = 9 To 384 ' note the number range
If Range("BF" & R).Value = "-" Then
Range("BF" & R).Interior.Color = vbWhite
' Greater than 5% less
Else
If Range("BF" & 385).Value * (1 + Pcent) < Range("BF" & R).Value Then
Range("BF" & R).Interior.Color = vbRed
Else
' greater than 5% more
If Range("BF" & 385).Value > Range("BF" & R).Value * (1 + Pcent) Then
Range("BF" & R).Interior.Color = vbGreen
Else: Range("BF" & R).Interior.Color = vbWhite
End If
End If
End If
Next R
End Sub


Help please if you can xxx
Amy xx

 

[Toppers]

Hi Again,
Re your last post: pcnt=0.05 if you mean 5%



Sub TrafficLights()
Dim R As Integer
Dim Pcent As Double
Pcent = 0.5
For C = 58 To 88
For R = 9 To 384 ' note the number range
If Cells(R, C).Value = "-" Then
Cells(R, C).Interior.Color = vbWhite
' Greater than 5% less
Else
If Cells(385, C).Value * (1 + Pcent) < Cells(R, C).Value Then
Cells(R, C).Interior.Color = vbRed
Else
' greater than 5% more
If Cells(385, C).Value > Cells(R, C).Value * (1 + Pcent) Then
Cells(R, C).Interior.Color = vbGreen
Else: Cells(R, C).Interior.Color = vbWhite
End If
End If
End If
Next R
Next C
End Sub

 

[AmyTaylor]

Toppers - thank you very much for your post, very helpful !

xx