Find new range
Results 1 to 9 of 9

Thread: Find new range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2012
    Location
    MEX
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Find new range

    Hi all

    In range A3:S33, I use this code to highlight a row.

    Code:
    Dim row As Long, column As Long
    
    row = ActiveCell.row
    column = ActiveCell.column
    
    If row < 3 Or row > 33 Or column > 19 Then
        Range("A3:S33").Interior.ColorIndex = xlNone
        Exit Sub
    Else
        Range("A3:S33").Interior.ColorIndex = xlNone
        Range("A" & row & ":S" & row).Interior.ColorIndex = 34
    End If

    However, every now and then I add records and range may vary. How should I edit code in order to adjust range? I mean auto find/identify new range?


    Thanks in advance, ColdGeorge

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,680
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Find new range

    You did not show us the complete script.
    Is this a sheet change event script.
    Show us all the code.

    And explain in words exactly what your trying to do.

    Are you trying to change the interior color of the active row
    Column A to S
    ??
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    Board Regular
    Join Date
    Aug 2012
    Location
    MEX
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find new range

    Hi

    Here's the complete scrip
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim row As Long, column As Long
    
    row = ActiveCell.row
    column = ActiveCell.column
    
    If row < 3 Or row > 33 Or column > 19 Then
        Range("A3:S33").Interior.ColorIndex = xlNone
        Exit Sub
    Else
        Range("A3:S33").Interior.ColorIndex = xlNone
        Range("A" & row & ":S" & row).Interior.ColorIndex = 34
    End If
    End Sub
    Yes, I am trying to change the interior color of the active row column A to S, but, sometimes I add new records (rows and columns) I don't want to edit code every time, that's why I'm asking for help.

    Thanks, ColdGeorge

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,680
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Find new range

    Try this:
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Modified  10/3/2018  9:39:49 AM  EDT
    Cells.Interior.ColorIndex = xlNone
    Cells(Target.row, 1).Resize(, 19).Interior.ColorIndex = 34
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  5. #5
    Board Regular
    Join Date
    Aug 2012
    Location
    MEX
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find new range

    Hi

    I really don't think your code do what I need, thanks.

    ColdGeorge

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,680
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Find new range

    Your original post said:
    I am trying to change the interior color of the active row column A to S,

    My code does that.

    Tell me what is it doing wrong.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  7. #7
    Board Regular
    Join Date
    Aug 2012
    Location
    MEX
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find new range

    Hi

    Post #1 says "In range A3:S33, I use this code to highlight a row.....However, every now and then I add records and range may vary. How should I edit code in order to adjust range? I mean auto find/identify new range?"

    I don't need help to highlight row, the code I use does that. But, I need help to prevent editing code every time I add a new record. I mean adding rows and columns. Is there a way to recognize new range when I add a record?

    Thanks, ColdGeorge

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,680
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Find new range

    The code I provided will work on any row on the sheet.

    Are you saying you only want this to apply in the range you use.
    And you keep saying as I add more rows and columns

    You are not able to add more rows and columns.

    Excel has 1.5 million rows and many columns I forget the number.

    You said earlier the active row
    You did not say the row must have data
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,680
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Find new range

    Are you wanting all rows with data colored?
    And if there is no data on the row you do not want it colored

    So if row 44 has any data entered you want that row colored
    From Column A to S

    Or are you wanting:

    If row 45 has data in columns A to G

    You want row 45 columns A to G colored
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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