looping across columns in range?
 

Can someone help me here, we have the following VBA code:

Sub TrafficLight()
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

It is working fine, however, as you can see it only works for range BF
at the moment, when I want it to run the same calculations from column
BF to column CJ.

Al;l help gratefully received.

Scott

See the solution to your similar post in Excel.Programming

Regards

Trevor


"Amy" wrote in message
oups.com...
Can someone help me here, we have the following VBA code:

Sub TrafficLight()
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

It is working fine, however, as you can see it only works for range BF
at the moment, when I want it to run the same calculations from column
BF to column CJ.

Al;l help gratefully received.

Scott

Hi Scott,

Have you tried conditional formatting?

Thanks,
Matt

"Amy" wrote:

Can someone help me here, we have the following VBA code:

Sub TrafficLight()
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

It is working fine, however, as you can see it only works for range BF
at the moment, when I want it to run the same calculations from column
BF to column CJ.

Al;l help gratefully received.

Scott

Try this...


Dim C As Integer
Dim R As Integer
Dim Pcent As Double
Pcent = 0.5

For C = Range("BF1").Column To Range("CJ1").Column 'Can change columns to
include from here
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(R, C).Value * (1 + Pcent) < Cells(R, C).Value Then
Cells(R, C).Interior.Color = vbRed
Else
' greater than 5% more
If Cells(R, 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


HTH,
Matt

"Amy" wrote:

Can someone help me here, we have the following VBA code:

Sub TrafficLight()
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

It is working fine, however, as you can see it only works for range BF
at the moment, when I want it to run the same calculations from column
BF to column CJ.

Al;l help gratefully received.

Scott