Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- AmyTaylor ------------------------------------------------------------------------ AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970 View this thread: http://www.excelforum.com/showthread...hreadid=388458 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 View this thread: http://www.excelforum.com/showthread...hreadid=388458 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
referencing a column
Toppers - thank you very much for your post, very helpful ! xx -- AmyTaylor ------------------------------------------------------------------------ AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970 View this thread: http://www.excelforum.com/showthread...hreadid=388458 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing column to row across worksheets | Excel Worksheet Functions | |||
Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
Referencing whole column | Excel Discussion (Misc queries) | |||
Column REferencing is out of wack | Excel Programming | |||
Referencing Any Cell in a Column | Excel Programming |