referencing a column
 

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


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

referencing a column
 

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" wrote:


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


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

referencing a column
 

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

xx


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