vba - jump to the next colored cell

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question


    Hi there,

    I am a beginner in VBA, but I am really trying to get the hang of it.

    I am looking for the right code to jump to the next colored cell, whatever color or location it is.

    I can do this without VBA, using the control f function, but I want to has this in code.

    So I tried recording it, in order to get the code, but since it references a pre-selected color (which I select), this only lasts as long as the program is currently open with THAT particular find option in place. So when I save, and reopen, the macro will NOT reference that previously colored cell.

    This may be a little long winded, but I wrote this, so no one should say "ok, just record the control f function".

    So please, what is the simplest way in vba (in a module) to skip/jump/goto the next colored cell???

    thanks a bunch.?(

  • Hi and Welcome to the Forum :)


    When it comes to colored cells ... there is a major difference between the cells colored manually ... and the ones which are colored as a result of conditional formatting ...


    Is it a question to jump to the next data entry cell ...?


    Could you attach a sample file ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Carim

    Changed the title of the thread from “excel vba” to “vba - jump to the next colored cell”.
  • Quote

    So I tried recording it, in order to get the code, but since it references a pre-selected color (which I select), this only lasts as long as the program is currently open with THAT particular find option in place. So when I save, and reopen, the macro will NOT reference that previously colored cell.

    Macros don't have memories, Variables will need resetting each time the macro is run.


    Post your code, remember to use Code Tags or attach an example workbook with the code

  • thanks Carim for responding.

    your example jumped around to the squares that YOU put into cell "J1".

    But notice in my question above it states "I am looking for the right code to jump to the next colored cell, whatever color or location it is."

    the jump is to be based on color, not ranges. And any color. And I am talking about manually filled colors, not conditional formatting.

  • You are welcome :)


    To come up with a tailor-made proposal ... why don't you attach a sample file to help illustrate your objective ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I understand.

    But I don't want a tailor-made proposal.-

    I want to run a macro, on any sheet, and when the macro is run it will go to the first colored cell.

    That is why I don't think there is any use in attaching a sample file. because regardless of where the cell is, I want the vba code to go to this first ( or nearest) colored cell.

    I hope I have made myself clear enough

  • Proposed macro ...

    Code
    1. Sub ColoredCells()
    2. Dim c As Range
    3. ' To be adapted to your own situation
    4. For Each c In Sheet1.Range("E1:E20")
    5. If c.Interior.ColorIndex > 0 Then Application.Goto Cells(c.Row, c.Column): MsgBox "Next..."
    6. Next c
    7. End Sub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I appreciate your efforts, Carim.

    Before I try your code, do I have to declare a variable as a range? if I do what is the range for a whole sheet?

    is there a "find" vba equivalent? maybe if I specified a color, could vba "find" that color?

  • I appreciate your efforts, Carim.

    Before I try your code, do I have to declare a variable as a range? if I do what is the range for a whole sheet?

    is there a "find" vba equivalent? maybe if I specified a color, could vba "find" that color?

    May be you will better understand ... now ... the expression a " tailor-made proposal " ...


    It is tailor-made macro .... fully adapted to the specifics of your actual worksheet ... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Got it!!!

    it is Range(Cells.Address) for all the cells.

    I tried this and it worked however after the last cell it went on an endless search.

    this maybe better

    range .UsedRange.Select ? is this a valid command?

  • Hi everyone,

    I am NOT out to annoy anyone!! sorry.

    I just discovered what "CROSS POST" means. So please have patience with me.

    here is a link to where I have posted this question also.

    And I accept on myself until my dying day, to notify all the public places that I have put forth questions.

    I hope I received forgiveness and acceptance.



    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    https://chandoo.org/forum/thre…d-cell.45860/#post-273238

  • yes.


    this is an answer I received from p45cal, in the link I posted above.

    THANK-YOU P45CAL.