Results 1 to 6 of 6

Help looping until a certain cell value

Threaded View

gryffin13 Help looping until a certain... 06-28-2011, 02:19 PM
Mordred Re: Help looping until a... 06-28-2011, 02:30 PM
gryffin13 Re: Help looping until a... 06-28-2011, 02:34 PM
Mordred Re: Help looping until a... 06-28-2011, 02:37 PM
Mordred Re: Help looping until a... 06-28-2011, 02:45 PM
gryffin13 Re: Help looping until a... 06-29-2011, 01:46 PM
  1. #1
    Registered User
    Join Date
    05-25-2011
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    52

    Help looping until a certain cell value

    Ok, this is going to be easy for anyone comfortable with vba to answer. I however am a beginner. My goal is simple, but I wanted to do it through code, just to challenge myself. What I want to do is go down the row and if column E is a certain value, highlight A-E in that row to be a certain color. So if E3 is the number 2, I want A2:E2 to be highlighted orange. Likewise if E7 is the number 4 I want A7:E7 to be highlight blue. Make sense? Alright here's the code I wrote to attempt to make it work:

    Sub priority()
    '
    ' priority Macro
    '
    
    '
    Dim cellnumber As Integer
        For cellnumber = 5 To 144
    
            Range("E & cellnumber").Select
            If Selection.Value = 1 Then
            Range("A& cellnumber:E& cellnumber").Select
                With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 192
                .TintAndShade = 0
                .PatternTintAndShade = 0
                End With
            ElseIf Selection.Value = 2 Then
            Range("A& cellnumber:E& cellnumber").Select
                With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 49407
                .TintAndShade = 0
                .PatternTintAndShade = 0
                End With
            
            ElseIf Selection.Value = 3 Then
            Range("A& cellnumber:E& cellnumber").Select
                With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 5296274
                .TintAndShade = 0
                .PatternTintAndShade = 0
                End With
             ElseIf Selection.Value = 4 Then
            Range("A& cellnumber:E& cellnumber").Select
                With Selection.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 15773696
                .TintAndShade = 0
                .PatternTintAndShade = 0
                End With
                    
            End If
            
        Next
        
    End Sub
    I know where I'm going wrong. It's the "Range("A& cellnumber:E& cellnumber").Select" part. The problem is that I don't know how to say, if x = 7 select E7. I know there must be a simple way to do it. I remember from reading something that the & operator joined things, but clearly my guess at the syntax here isn't correct. Thanks for taking your time to help.
    Last edited by gryffin13; 06-29-2011 at 01:46 PM.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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