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

 

[Matt Lunn]

Hi Scott,

Have you tried conditional formatting?

Thanks,
Matt

 

[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