Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    409
    Thanks
    1
    Thanked 0 Times in 0 Posts

    block cells macro (excel xp)

    I would like to have a macro block consecutive cells in a column based on the contents of the adjacent cells. I would like to start at a cell and highlight the cells below it as long as the contents of the adjacent cell are the same as the contents of the first adjacent cell. For example:

    Cell Cell Contents
    A1: Different
    A2: Same
    A3: Same
    A4: Same
    A5: Same
    A6: Same
    A7: Same
    A8: Different

    B1: 425
    B2: 221
    B3: 380
    B4: 488
    B5: 5444
    B6: 6789
    B7: 7456
    B8: 8987

    In this case I would like to start at B2 and have cells highlighted until the contents in column A are different from A2; e.g. highlight B2 to B7. Any suggestions? Thanks!

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

    Re: block cells macro (excel xp)

    Do you want the macro to ask for the cell to start with, or should it be determined automatically? If the latter, what if there are multiple ranges in column A with the same values in consecutive cells?

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    409
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: block cells macro (excel xp)

    1. My example should have shown blank cells above B2. In the real data, the cells are blank above the start cell and I am using the keystroke equivalent of {end down} to get to the start cell, in this case, B2.
    2. The data will not have multiple ranges in column A with the same values. In the real data, column A contains title of record (CD) albums.

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

    Re: block cells macro (excel xp)

    You can do this without macro code, but using conditional formatting instead.
    - Select B2:B8 or as far down as needed.
    - Select Format | Conditional Formatting...
    - Select 'Formula Is' from the first dropdown list.
    - Enter <code>=(A2=A1)</code> in the next box. This formula is for B2; it will be adjusted automatically for the cells below.
    - Click Format...
    - Activate the Patterns tab.
    - Select the desired highlight color.
    - Click OK twice.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Posts
    409
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: block cells macro (excel xp)

    Thanks for the reply. I like the solution, however, I wasn't explicit enough in my question. I would like to block the data because I am going to move and transpose the data. So the problem is how to automatically do the keystroke equivalent of holding the shift key down and hitting the down arrow until I have blocked all of the data for which column A cells are the same. (I've figured out the move and transpose part once the range has been blocked.)

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

    Re: block cells macro (excel xp)

    Sorry for misunderstanding your question. Here is a macro to do what you want. It assumes that you have already selected the first cell.

    Sub HighlightSame()
    Dim lngRow As Long
    Dim lngStartRow As Long
    lngStartRow = ActiveCell.Row
    lngRow = lngStartRow
    Do While ActiveSheet.Cells(lngRow + 1, 1) = ActiveSheet.Cells(lngStartRow, 1)
    lngRow = lngRow + 1
    Loop
    ActiveSheet.Range("B" & lngStartRow & ":B" & lngRow).Select
    End Sub

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Posts
    409
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: block cells macro (excel xp)

    Works like a charm! Thanks for your time and effort.

Posting Permissions

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