Next and Previous buttons to navigate between cells with a yellow background

Question

Next and Previous buttons to navigate between cells with a yellow background

Navigation

#1 by (1 votes)

1

I would like to ask your help to make two navigation buttons, in the "next" button and the "previous" button, the thing is this: I have this search engine

And each time the user clicks the "Next" button the program should go to the next cell colored yellow and make it the active cell (so that it can be modified) and if it gives back to "next" must go to the next cell painted yellow and so on, and for the "Previous" button, it would be to return to the last cell where it was. So far I know that the condition you should use to look for the colored cells is

Dim Clr As Long Dim rng As Range Dim Cell As Range Clr = vbYellow Rng = Activesheet.Range("A3:P88") For Each Cell in Selection If Rng = rng.Interior.Color = Clr Then Cell = Activecell.Select

And then I do not know what to do to mark it as the active cell, and after that move on to the next cell.

vba macros excel

asked by 23.04.2018 в 16:43

source

1 answer

Edit and delete mysql records from datatable Problem with sql query in only_full_group_by mode

score 1 · Accepted Answer

Use the macro recorder

Example of the result of using the recorder to find the next yellow cell

Sub Siguiente()
'
' Siguiente Macro
'

'
    With Application.FindFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Activate
End Sub

Then to the previous code we changed the name of the Sub and SearchDirection: = xlNext by SearchDirection: = xlPrevious

Sub Anterior()
'
' Anterior Macro
'

'
    With Application.FindFormat.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
        , SearchFormat:=True).Activate
End Sub

Then assign each macro to the corresponding button on your form by following the instructions in Assign a macro to a Form or Control button