+ Reply to Thread
Results 1 to 3 of 3

referencing a column

  1. #1
    Forum Contributor
    Join Date
    03-11-2005
    Posts
    115

    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

  2. #2
    Toppers
    Guest

    RE: 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. #3
    Forum Contributor
    Join Date
    03-11-2005
    Posts
    115

    Thank you Toppers

    Toppers - thank you very much for your post, very helpful !

    xx

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1