• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

go to next colored cell

J

joshgosh

New Member
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.
?(
 
Hui said:
Josh

Can you please attach a sample file
Click to expand...
thanks Hui, for replying
I do NOT want to send a sample file for the following reason. if you read carefully my question, you will understand that I want something that will work on ANY sheet, no matter where the colored cell is, and the method of the VBA will specifically to find the next colored cell. (whichever color it is)
 
Hi,​
​
without any sample attachment neither a complete crystal clear explanation​
so just activate the Macro Recorder and operate manually in order to get your own code base …​
​
 
p45cal said:
Can you tell us how you're doing this?
especially this bit:
Click to expand...


see attached
choose the option called Choose Format From Cell, and then select the cell with a color. once this is done you can find any cell with this color.
 

Attachments

  • choose format from cell.PNG
    choose format from cell.PNG
    111.5 KB · Views: 7
Marc L said:
Hi,​
​
without any sample attachment neither a complete crystal clear explanation​
so just activate the Macro Recorder and operate manually in order to get your own code base …​
​
Click to expand...
the base code I have. But like I mentioned, it does not last, because it relies on copying the color format of a particular cell. When exiting excel and reentering, the code will not work. see also my response to p45cal.
i
 
joshgosh said:
choose the option called Choose Format From Cell, and then select the cell with a color. once this is done you can find any cell with this color.
Click to expand...
You can use this feature to find the next specific color cell but not for the « whatever color » cell​
so for any background color cell you must loop cell by cell and chek if its color is different than a non color cell …​
​
 
The reason why this forum expects a workbook attachment with a complete crystal clear explanation according to its rules …​
​
 
it my case, no attachment is necessary, because i am after a solution with does not belong to a particular worksheet.
plus the fact that it is very easy to imagine my request.
the truth is, since i posted, I have already figured the code, but maybe someone has something better.
 
Last edited by a moderator:
A couple of routines which you can assign to buttons, or better, keyboard shortcuts:
Code: 
Sub SameColour()
If ActiveCell.Interior.ColorIndex = xlNone Then
  Set ss = Application.InputBox("Select cell with the same colour you're lookingfor", "Select a cell", Selection.Address, , , , , 8)
  ss.Activate
End If
Application.FindFormat.Clear
Application.FindFormat.Interior.Color = ActiveCell.Interior.Color
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True).Activate
End Sub
Code: 
Sub AnyColour()
Dim StartCellDefined As Boolean
Set ur = ActiveSheet.UsedRange
If Intersect(ur, ActiveCell) Is Nothing Then
  StartCellDefined = True
Else
  rw = ActiveCell.Row - ur.Row + 1
  Colm = ActiveCell.Column - ur.Column + 1
End If
For myRw = 1 To ur.Rows.Count
  For myColm = 1 To ur.Columns.Count
    If Not StartCellDefined Then
      myRw = rw
      myColm = Colm + 1
      StartCellDefined = True
    End If
    If ur.Cells(myRw, myColm).Interior.ColorIndex <> xlNone Then
      ur.Cells(myRw, myColm).Activate
      Exit Sub
    End If
  Next myColm
Next myRw
For myRw = 1 To ur.Rows.Count
  For myColm = 1 To ur.Columns.Count
    If myRw = rw And myColm = Colm Then
      EndReached = True
      Exit For
    End If
    If ur.Cells(myRw, myColm).Interior.ColorIndex <> xlNone Then
      ur.Cells(myRw, myColm).Activate
      Exit Sub
    End If
  Next myColm
  If EndReached Then Exit For
Next myRw
End Sub
SameColour will jump to the next cell of the same colour as the active cell (and if the active cell has no colour it will ask you to select a cell with the same colour as you want to jump to (which can still be of no colour!).

AnyColour will jump to the next cell which has a colour (although be aware that some cells may appear to have no colour when they do).
 
Last edited:
p45cal said:
Since posting, I spotted a mistake in the AnyColour sub which has now been rectified in that post.
Click to expand...

thank-you p45 cal,
i do appreciate your post, and i put you a like
here is a another way to get the next colored cell


Code: 
Sub ColoredCells()
Dim c As Range
' To be adapted to your own situation
 For Each c In Sheet1.Range(Cells.Address)
 If c.Interior.ColorIndex > 0 Then Application.Goto Cells(c.Row, c.Column): MsgBox "Next..."
 Next c
End Sub
 
That ColoredCells macro is inconvenient at multiple levels:
  • It doesn't fit this:
    joshgosh said:
    i am after a solution with does not belong to a particular worksheet
    Click to expand...
    because it only looks at Sheet1
  • Having found a cell on Sheet1 with a colour, it then uses the unqualified reference Cells(c.Row, c.Column) which means if the active sheet is not Sheet1 it will select the corresponding cell on the active sheet (or if the code is in a sheet's code-module the corresponding cell on that sheet)
  • Having selected a cell it pops up a message box with no choices, only OK, so you can't escape from the loop until the whole sheet has been processed. This means you can only look at the selected cell and not do anything with it.
  • It always starts at the first cell with colour on the whole sheet rather than carry the search on from the currently active cell.
  • It looks at the whole sheet, all 17,179,869,184 cells in it, instead of just the used range.
Otherwise:
Sheet1.Range(Cells.Address) might as well be Sheet1.Cells
Application.Goto Cells(c.Row, c.Column) could be Application.Goto c

The comment in the code 'To be adapted to your own situation is highly applicable.
 
p45cal said:
That ColoredCells macro is inconvenient at multiple levels:
  • It doesn't fit this:because it only looks at Sheet1
  • Having found a cell on Sheet1 with a colour, it then uses the unqualified reference Cells(c.Row, c.Column) which means if the active sheet is not Sheet1 it will select the corresponding cell on the active sheet (or if the code is in a sheet's code-module the corresponding cell on that sheet)
  • Having selected a cell it pops up a message box with no choices, only OK, so you can't escape from the loop until the whole sheet has been processed. This means you can only look at the selected cell and not do anything with it.
  • It always starts at the first cell with colour on the whole sheet rather than carry the search on from the currently active cell.
  • It looks at the whole sheet, all 17,179,869,184 cells in it, instead of just the used range.
Otherwise:
Sheet1.Range(Cells.Address) might as well be Sheet1.Cells
Application.Goto Cells(c.Row, c.Column) could be Application.Goto c

The comment in the code 'To be adapted to your own situation is highly applicable.
Click to expand...

Again, thank-you for your input.
i am studying carefully what you have wrote. And you are right.
my question to you is: Can these inconveniences be modified? because this code is much shorter than your code. So maybe you could agree that if correct modifications were made, then it would be useful.
And even if we cannot change it to correct all the problems, I guess some of them could be solved. like the message box could have a quit option or even no option at all. And instead of sheet1, use a activesheet option. and use a used.range option etc...
Either way, I am happy and I have learned something very useful to me. And I thank-you very much for your feedback.
 
joshgosh said:
Can these inconveniences be modified?
Click to expand...
Yes
joshgosh said:
because this code is much shorter than your code
Click to expand...
Be aware that shorter code is not necessarily faster code - as you'll see below.
joshgosh said:
So maybe you could agree that if correct modifications were made, then it would be useful.
Click to expand...
Mostly, but you'd still be left with a major inconvenience:
Let's say you have a sheet with 100 coloured cells that you want to check, and some you may want to edit, so you run the macro and it finds the first coloured cell, it's OK so 'Next', until the 5th cell which needs editing, so you 'Quit', edit the cell then run the macro to check the 6th cell. Now you have to iterate through the first 5 cells, pressing 'Next' each time until you get to the 6th cell. A bit of a pain, but not too bad. Let's say all the other cells are fine except for the 98th and 99th coloured cells, So you diligently press 'Next' 97 times, 'Quit' on the 98th, edit it, now on to the 99th - oh no, I've got to click through 98 'Nexts' to get to the 99th. Now it is a big pain.
So my offering was to start from wherever you are on the sheet to find the next coloured cell, whilst not forgetting to go back to the start if you're near the end of your sheet.
While Range.Find, with its After:=ActiveCell argument would be great for that (see the SameColour macro) I don't know how to make it look for cells with any colour; it's good at looking for a specific colour, or no colour at all. So I resorted to iterating through all cells in the UsedRange, starting from where the active cell is, to the end, then going back to the beginning and searching up to the active cell again.
I used two very similar loops here:
Code: 
For myRw = 1 To ur.Rows.Count
'ask some questions
Next myRw
I could have been cleverer because they're very similar loops, but they're asking different questions. I could have asked all the questions in one loop, but run it twice by putting it into its own loop (this would also mean that if I later wanted to modify what it looked for (font instead of colour, say) then I'd only have to modify the code in one place, not two):
Code: 
For z = 1 to 2
    For myRw = 1 To ur.Rows.Count
    'ask a few more questions
    Next myRw
Next z
but it would have been slower, because it would be asking 3 or 4 questions in the innermost loop instead of just 2, without reducing the number of loops. Imagine if your sheet had something in cell A1, and a coloured cell at cell XFC1048574 somewhere in the bottom right hand corner; I'd be iterating through the inner loop some 34 billion times whereupon the difference between asking 2 questions and 3 or 4 might make a significant difference to the user experience.

So because of all this I'm not going to make those modifications.

By the way, the SameColour macro might be speeded up a bit by using:
Activesheet.UsedRange.Find(What:="", After:…etc.
instead of:
Cells.Find(What:="", After:…etc.
(I said 'might' because I'm not sure if Excel, behind the scenes, is clever enough only to look at the used range; I suspect so.)
 
Last edited:
p45cal said:
Yes
Be aware that shorter code is not necessarily faster code - as you'll see below.
Mostly, but you'd still be left with a major inconvenience:
Let's say you have a sheet with 100 coloured cells that you want to check, and some you may want to edit, so you run the macro and it finds the first coloured cell, it's OK so 'Next', until the 5th cell which needs editing, so you 'Quit', edit the cell then run the macro to check the 6th cell. Now you have to iterate through the first 5 cells, pressing 'Next' each time until you get to the 6th cell. A bit of a pain, but not too bad. Let's say all the other cells are fine except for the 98th and 99th coloured cells, So you diligently press 'Next' 97 times, 'Quit' on the 98th, edit it, now on to the 99th - oh no, I've got to click through 98 'Nexts' to get to the 99th. Now it is a big pain.
So my offering was to start from wherever you are on the sheet to find the next coloured cell, whilst not forgetting to go back to the start if you're near the end of your sheet.
While Range.Find, with its After:=ActiveCell argument would be great for that (see the SameColour macro) I don't know how to make it look for cells with any colour; it's good at looking for a specific colour, or no colour at all. So I resorted to iterating through all cells in the UsedRange, starting from where the active cell is, to the end, then going back to the beginning and searching up to the active cell again.
I used two very similar loops here:
Code: 
For myRw = 1 To ur.Rows.Count
'ask some questions
Next myRw
I could have been cleverer because they're very similar loops, but they're asking different questions. I could have asked all the questions in one loop, but run it twice by putting it into its own loop (this would also mean that if I later wanted to modify what it looked for (font instead of colour, say) then I'd only have to modify the code in one place, not two):
Code: 
For z = 1 to 2
    For myRw = 1 To ur.Rows.Count
    'ask a few more questions
    Next myRw
Next z
but it would have been slower, because it would be asking 3 or 4 questions in the innermost loop instead of just 2, without reducing the number of loops. Imagine if your sheet had something in cell A1, and a coloured cell at cell XFC1048574 somewhere in the bottom right hand corner; I'd be iterating through the inner loop some 34 billion times whereupon the difference between asking 2 questions and 3 or 4 might make a significant difference to the user experience.

So because of all this I'm not going to make those modifications.

By the way, the SameColour macro might be speeded up a bit by using:
Activesheet.UsedRange.Find(What:="", After:…etc.
instead of:
Cells.Find(What:="", After:…etc.
(I said 'might' because I'm not sure if Excel, behind the scenes, is clever enough only to look at the used range; I suspect so.)
Click to expand...
I have to say that I admire your thoroughness of what goes into a macro. :awesome:
i am going to study your answer carefully, as i believe it is a great lesson in building macros.
Again, thank-you for your time and patience.
 
You must log in or register to reply here.
Back
Top