# referencing a column

1. ## 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

2. ## RE: 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
>
>

3. ## Thank you Toppers

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

xx

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1