looping across columns in range?

[Amy]

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

[Trevor Shuttleworth]

See the solution to your similar post in Excel.Programming

Regards

Trevor


"Amy" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Matt Lunn]

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
>
>

[Matt Lunn]

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
>
>