macro to highlight specific cells down the worksheet

The objective is have a two button - one button in cell F1 and and one button in cell G1 (Row A will be frozen)
A click on the button in F1 will highlight the next 350 rows from cursor position, for columns A,B,C & D.
A click on the button in G1 will highlight the prior 350 rows from cursor position, for columns A,B,C & D.
gregfthompsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
"The objective is have a two button - one button in cell F1 and and one button in cell G1 (Row A will be frozen)"

Row #1, or Column [A]?
Roy CoxGroup Finance ManagerCommented:
Code for button 1

Selection.Resize(350, 4).Select

Open in new window


Code for button 2

Selection.Offset(-1).Resize(349, 4).Select

Open in new window

Rgonzo1971Commented:
Hi,

pls try

Sub MacroBefore()
ActiveSheet.UsedRange.Interior.Pattern = xlNone
If ActiveCell.Row > 2 Then
    Range("A" & Application.Max(2, ActiveCell.Row - 350) & ":D" & ActiveCell.Row - 1).Interior.Color = vbYellow
End If
End Sub

Sub MacroAfter()
LastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row
ActiveSheet.UsedRange.Interior.Pattern = xlNone
If ActiveCell.Row <= LastRow Then
    Range("A" & ActiveCell.Row + 1 & ":D" & Application.Min(ActiveCell.Row + 350, LastRow)).Interior.Color = vbYellow
End If
End Sub

Open in new window

Regards
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

[ fanpages ]IT Services ConsultantCommented:
A click on the button in F1 will highlight the next 350 rows from cursor position, for columns A,B,C & D.
A click on the button in G1 will highlight the prior 350 rows from cursor position, for columns A,B,C & D.

Both of your suggestions select subsequent rows, Roy_Cox.
gregfthompsonAuthor Commented:
sorry  fanpages.  Row 1 will be fozen.
[ fanpages ]IT Services ConsultantCommented:
Thanks.

Do you wish to ignore row 1 from the selection (where applicable)?
gregfthompsonAuthor Commented:
I've attached a file showing the requirement.
I could not create the buttons from any of the suggested scripts.

Hope this helps.

Thanks,

Greg
highlight-example-requirement.xlsx
[ fanpages ]IT Services ConsultantCommented:
Thanks Greg.

I would expect those that posted suggestions so far did not appreciate you were asking how to create the buttons, but assumed you already knew how to do this.

Also, to confirm what I asked earlier; it seems that you did intend row 1 to be ignored from the selection made.

I will let those that have already posted comments amend their own suggestions, as necessary.
Rgonzo1971Commented:
Hi,

pls try

Sub MacroBefore()

If ActiveCell.Row > 2 Then
    Range("A" & Application.Max(2, ActiveCell.Row - 350) & ":D" & ActiveCell.Row - 1).Select
End If
End Sub

Sub MacroAfter()
    Range("A" & ActiveCell.Row + 1 & ":D" & ActiveCell.Row + 350).Select
End Sub

Open in new window


EDIT or maybe

Sub MacroBefore()

If ActiveCell.Row > 1 Then
    Range("A" & Application.Max(2, ActiveCell.Row - 349) & ":D" & ActiveCell.Row).Select
End If
End Sub

Sub MacroAfter()
    Range("A" & ActiveCell.Row & ":D" & ActiveCell.Row + 349).Select
End Sub

Open in new window

highlight-example-requirement.xlsm
Roy CoxGroup Finance ManagerCommented:
When you mentioned from the cursor I assumed that you meant from  the ActiveCell.

Here's instructions on adding buttons

Is this what you wanted, I have used two transparent shapes instead of buttons and assigned macros to them
highlight-example-requirement--1-.xlsm
gregfthompsonAuthor Commented:
Hi Roy_Cox,

The link to creating button opened at a "This webpage is not available"

Thanks,

Greg
gregfthompsonAuthor Commented:
Hi Roy_Cox,

PS: The attached example did not work for me (not sure if it was meant to be a working example).

Thanks,

Greg
Roy CoxGroup Finance ManagerCommented:
Here's the link , I'm not sure why it didn't work previously

Add a button

I've attached the workbook because that link in the previous post did not seem to work.
highlight-example-requirement--1-.xlsm
gregfthompsonAuthor Commented:
Thanks for your efforts.
I did not think this through.

The objective is to be able to use a click on the down button to select the next 350 rows x A,B,C and D columns,  from the whichever bottom row where the existing selection is currently located.

And the opposite for the up button.

Hope this makes sense.
gregfthompsonAuthor Commented:
PS Thanks Roy_Cox for the link to making buttons.
gregfthompsonAuthor Commented:
PPS The Up button would select the 350 rows above the top row from the current selection.
Roy CoxGroup Finance ManagerCommented:
That's what I thought you wanted. It will need error handling to in case there are not sufficient rows  I'll amend the code
Rgonzo1971Commented:
Hi,

Have you tried my code above?
Roy CoxGroup Finance ManagerCommented:
Replace the code with this

Option Explicit

Sub HighligtDown()
    If Selection.Cells.Count > 1 Then
        MsgBox "Please select a single cell", vbCritical, "Try again"
        Exit Sub
    End If
    Selection.Resize(350, 4).Select
End Sub

Sub HighligtUp()
    If Selection.Cells.Count > 1 Then
        MsgBox "Please select a single cell", vbCritical, "Try again"
        Exit Sub
    End If
    If Selection.Row < 351 Then
        MsgBox "Insufficient rows above to select", vbCritical, "Try again"
        Exit Sub
    End If
    Selection.Offset(-349).Resize(350, 4).Select
End Sub

Open in new window



Let me know if this is what you mean
gregfthompsonAuthor Commented:
Hi RGonzo1971,
I could not get it to work.
Please see note below.
Sorry for not being clear.

Roy_Cox,
This highlights the next 350 rows as required. Can you make it so that another click will select the next 350 rows and another click selects the next 350 rows so that the highlighted section moves down the worksheet with each click, and move the page so that the highlighted section appears on screen?
Sorry for not being clear.
Thanks,

Greg
Roy CoxGroup Finance ManagerCommented:
Please in future ask your question clearly and completely.  The code that I provided works based on your request that it uses the ActiveCell as the starting point. Once the code runs there are 1400 cells selected, to work how you now want it would require the code to distinguish that it has already been run once.

See if this code works how tou want

Option Explicit

Sub HighligtDown()
    Select Case Selection.Count
    Case 1
        Selection.Resize(350, 4).Select
    Case 1400
        Cells(Selection.Rows.Count + 1, 1).Select
        Selection.Resize(350, 4).Select
    Case Is < 1400
        MsgBox "Please select a single cell", vbCritical, "Try again"
        Exit Sub
    End Select

End Sub

Sub HighligtUp()

    If Selection.Row < 351 Then
        MsgBox "Insufficient rows above to select", vbCritical, "Try again"
        Exit Sub
    End If
    Select Case Selection.Count
    Case 1
        Selection.Offset(-349).Resize(350, 4).Select
    Case 1400
        Cells(Selection.Rows.Count + 1, 1).Select
        Selection.Offset(-349).Resize(350, 4).Select
    Case Is < 1400
        MsgBox "Please select a single cell", vbCritical, "Try again"
        Exit Sub
    End Select
End Sub

Open in new window

gregfthompsonAuthor Commented:
I'm sorry. I am no good at describing. I understand your frustration.

I do not want to select 1400 rows x 4 columns.

I am seeking to have the next area of 350 rows x 4 columns selected each time the down button is clicked.
The first click would select  A2 to D351.  
The next click would deselect A2 to D351 and select A352 to D701
The next click would deselect A352 to D701 and select A702 to D1051
and so on

I hope this is a better description.

Thanks,

Greg
Roy CoxGroup Finance ManagerCommented:
I have currently made the code select blocks of 350, but tarting from the last row in each block. So willl the code always start with A2, because I believe that is what I first assumed.

I'll amend the code later
gregfthompsonAuthor Commented:
Thanks.
Can the code start from wherever the highlighted block is located?
gregfthompsonAuthor Commented:
Or how can I adjust the code to start at a specific A column cell?
Roy CoxGroup Finance ManagerCommented:
The code currently starts from the selected cell

I have amended the code to move down 350 rows one row at a time and to move up one row at a time. Try this

Option Explicit

Sub HighligtDown()
    Select Case Selection.Count
    Case 1
        Selection.Resize(350, 4).Select
    Case 1400
        Selection.Cells(2, 1).Select
        Selection.Resize(350, 4).Select
    Case Is < 1400
        MsgBox "Please select a single cell", vbCritical, "Try again"
        Exit Sub
    End Select

End Sub

Sub HighligtUp()
    If Selection.Cells(Selection.Rows.Count, 1).Row < 352 Then
        MsgBox "Insufficient rows above to select", vbCritical, "Try again"
        Exit Sub
    End If
    Select Case Selection.Count
    Case 1
        Selection.Offset(-349).Resize(350, 4).Select
    Case 1400
        Selection.Cells(Selection.Rows.Count - 1, 1).Select
        Selection.Offset(-349).Resize(350, 4).Select
    Case Is < 1400
        MsgBox "Please select a single cell", vbCritical, "Try again"
        Exit Sub
    End Select
End Sub

Open in new window

gregfthompsonAuthor Commented:
Thanks Roy_Cox.

Can you make the code to move up or down 350 rows at a time, so the next block of 350 rows is immediately above of below the preceding block?
Roy CoxGroup Finance ManagerCommented:
That's what the last amended code does. Select A2 and click move down, it will highlight a block of 350 rows. Click again and the block will start in A3,  The other button works in reverse
gregfthompsonAuthor Commented:
Thanks Roy_Cox.

I'm sorry that I did not explain ti correctly.

The next block needs to start at A351.

Is this possible?

Thanks,

Greg
Roy CoxGroup Finance ManagerCommented:
I've changed this code so many times. I'm sure that one of the earlier posts did this already!! See

2015-09-01 at 06:25:51ID: 40955979  above.

Please test the code and see what it is doing
gregfthompsonAuthor Commented:
Sorry.  I don't know enough.

I'll ask the question again.
[ fanpages ]IT Services ConsultantCommented:
"I'll ask the question again"

Just review each of the successive code listings that Roy_Cox has kindly already provided.

If, after reviewing each, you find one suits your requirements, then go with that.
If none do exactly what you want, find the closest, & explain what you would like to achieve that is different to what has already been provided.

I understand that sometimes it is difficult to confirm what you what until you see something in a demonstrable form, but it is unfair on Roy' to continue as is happening.

I am sure the code you want is already above... or something very close to it.
gregfthompsonAuthor Commented:
Thank you.

I don't mean to frustrate Roy_Cox.

I cannot get the code to run.

Is is possible to have a working file uploaded?
Roy CoxGroup Finance ManagerCommented:
I'll upload an example this evening
Roy CoxGroup Finance ManagerCommented:
I've reviewed all you replies and come up with this code that moves down in blocks of 350 rows and moves up by 350 rows.

Start in A2 and click the down button a few times.

Then leave the final selection selected and click up. the code will move upwards by 350 rows
highlight-example-requirement--1-.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gregfthompsonAuthor Commented:
Brilliant. Thanks heaps.

Apologies for the poor description to start.
Roy CoxGroup Finance ManagerCommented:
Glad we got there in the end.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.