Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
Can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
extract data from a range of cells in rows or columns when a date. | Excel Worksheet Functions | |||
Dynamic Print Range Help | Excel Worksheet Functions | |||
Define a range based on another named range | Excel Worksheet Functions |