Macro to scroll to the last coloured cell in a spreadsheet

[paulyankson]

Hi,
A question for the assembled Brains Trust:

- I have a personal spreadsheet
- let's say the range is A1:E200
- the cells A1:E100 have a yellow background that I have manually coloured in
- the cells A101:E200 don't have any coloured background
- all cells have data in them so I can't use a command to just go to the last populated cell in the column
- I need a macro command to move down to the last cell in the column that is coloured yellow
- eg: move down to cell A100 in the column and stop there
- a couple of days later one extra row will be highlighted with a yellow background
- eg: A1:E101 now
- so I'll need the cursor to utomatically move to the latest yellow cell that is coloured yellow
- eg: move down to cell A101 in the column and stop there

I'm using this macro at the moment ..... but it obviously takes me to the last empty cell in the entire sheet, ignoring the coloured background.

Thanks for any pointers!
Paul

Sub Macro1()
'
' Macro1 Macro
' sort au page, first by K column descending, then by E column ascending. then move down to first vacant spot in A column.
'

'
    Range("A8:A20").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A8:B3511").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("A8:K3511").Select
    ActiveWorkbook.Worksheets("au").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("au").Sort.SortFields.Add Key:=Range("K8:K3511"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("au").Sort.SortFields.Add Key:=Range("E8:E3511"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("au").Sort
        .SetRange Range("A8:K3511")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A8:A8").Select
Range(Selection, Selection.End(xlDown)).Select
    Range("A8").Select
    Selection.End(xlDown).Select
End Sub

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Assuming that your yellow is vbYellow try this. The code assumes that there will be at least one cell in column A that is coloured vbYellow

Sub LastYellow()
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = vbYellow
  Columns("A").Find(What:="", After:=Range("A1"), SearchDirection:=xlPrevious, SearchFormat:=True).Activate
  Application.FindFormat.Clear
End Sub

 

[paulyankson]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Assuming that your yellow is vbYellow try this. The code assumes that there will be at least one cell in column A that is coloured vbYellow

Sub LastYellow()
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = vbYellow
  Columns("A").Find(What:="", After:=Range("A1"), SearchDirection:=xlPrevious, SearchFormat:=True).Activate
  Application.FindFormat.Clear
End Sub

Hi Peter_SSs - wow - thank you so much! This is exactly what I was after and it works exactly as intended. Very much indebted 2U. Have been trying to resolve this for some time. Best regards from Australia, Paul.

 

[Peter_SSs]

You’re welcome. (I’m from Australia too )

 

[Joe4]

paulyankson,

Please note: When selecting a post as the solution, please select the actual original post that contains the solution, and not your own reply acknowledging that some other post is the solution. I have gone ahead and updated that on this thread for you.