Find new range

ColdGeorge · Oct 2, 2018

Hi all

[FONT=&quot]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

[/FONT]
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?

[FONT=&quot]
Thanks in advance, ColdGeorge
[/FONT]

My Aswer Is This · Oct 2, 2018

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
??

ColdGeorge · Oct 3, 2018

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

My Aswer Is This · Oct 3, 2018

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

ColdGeorge · Oct 3, 2018

Hi

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

ColdGeorge

My Aswer Is This · Oct 3, 2018

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.

ColdGeorge · Oct 3, 2018

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

My Aswer Is This · Oct 3, 2018

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

My Aswer Is This · Oct 3, 2018

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

Find new range

ColdGeorge

Active Member

Excel Facts

My Aswer Is This

Well-known Member

ColdGeorge

Active Member

My Aswer Is This

Well-known Member

ColdGeorge

Active Member

My Aswer Is This

Well-known Member

ColdGeorge

Active Member

My Aswer Is This

Well-known Member

My Aswer Is This

Well-known Member

Similar threads

Forum statistics

Share this page

Find new range

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock