Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting a range in VBA (2002/SP3)

    Hi,

    I have the following set of data in columns A and B with hundreds of rows (starting from row 2):

    A B C D
    1
    1 test#1 yes
    3 tes#2 yes
    4 test#3 no
    5 test#4 yes
    ...

    The data would be initially be in columns A and B, with more data entered in pairs in other columns. In VBA , how do I:
    1) Select Col A and B, and change the color in the range to red
    2) Go to the first cell in the active range and offset by 3 columns, and check if there any data in activecell. If activecell is not empty then color it blue.
    3) Repeat step 2, interchanging colors from green to red

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting a range in VBA (2002/SP3)

    I'm not sure I understand your question. You mention red and blue, then later on "interchanging colors from green to red".
    What you want can probably be done without code, using Conditional Formatting, but I'd like to understand what exactly you're trying to accomplish.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a range in VBA (2002/SP3)

    A typo. I meant to write "interchanging colors from blue to red".

    What I'm trying to do is to check the worksheet for data and make it visually easier to distiguish between the various pairs of data.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting a range in VBA (2002/SP3)

    Thanks, but I'm a bit dense today. Could you give some examples of what you expect the result to be? What should be red, what should be blue?

  5. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a range in VBA (2002/SP3)

    Sorry for not being clearer. I've attached a sample workbook. What I'm trying to do is check all worksheet in a workbook for data and if data exists colour it in order to make it easier to view.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting a range in VBA (2002/SP3)

    Sorry for being so stupid, but why would you need code for this? You can simply select columns A and B in their entirety and set text color to red, then select columns D and E and select blue, etc.
    This will take less than a minute and you have to do it only once.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a range in VBA (2002/SP3)

    I'm planning to write a VBA to manipulate the data pairs in each worksheet.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting a range in VBA (2002/SP3)

    OK, but the coloring doesn't depend on the data...

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting a range in VBA (2002/SP3)

    Does this do what you want?

    Sub ColorColumns()
    Dim c As Long
    Dim m As Long
    m = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    For c = 1 To m
    Select Case c Mod 6
    Case 1, 2
    Columns©.Font.Color = vbRed
    Case 4, 5
    Columns©.Font.Color = vbBlue
    End Select
    Next c
    End Sub

  10. #10
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a range in VBA (2002/SP3)

    True, coloring is just an extra thing. The procedure from your last post is useful, thanks.

    In the code that I have below, I want to go through each data pair and change some values. I start from from the pair in cell A2, and want to go to the next pair and so on. The way my code is written, I keep coming back to A2. How do I fix this?

    ' For/Next loop starts here here
    Set TableRange = ActiveSheet.Range("A2").CurrentRegion
    Set CellRange = TableRange.Cells(1, 1)
    CellRange.Offset(0, 3).Activate
    ' Next

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting a range in VBA (2002/SP3)

    Try something like this:

    Dim r As Long
    Dim m As Long
    Dim oCell1 As Range
    Dim oCell2 As Range
    ' Last filled row in column A
    m = Cells(Rows.Count, 1).End(xlUp).Row
    ' Loop through the rows, starting at row 2
    For r = 2 To m
    ' Cell in column A in the "current" row
    Set oCell1 = Cells(r, 1)
    ' And the cell in column D
    Set oCell2 = Cells(r, 4)
    ' Do something with the cells (without selecting them)
    ...
    Next r

  12. #12
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting a range in VBA (2002/SP3)

    Thanks Hans. That's what I was looking for. :-)

Posting Permissions

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