Select Next value that meets a criteria
up vote
0
down vote
favorite
I want to select cell by cell in column "A" which are filled by colour red(colour code=3). Each time a button is clicked selection should move to next cell which is filled in red (same column).
My Code so far:
Sub FindNext()
Dim c As Range
On Error Resume Next
With Worksheets(1).Range("A1:B500")
Set c = .Find(Cells.Interior.ColorIndex = 3, LookIn:=xlValues)
If Not c Is Nothing Then
Do
c.Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
excel vba excel-vba
edited Nov 12 at 8:27
Pᴇʜ
20.1k42650
asked Nov 12 at 7:35
Saj W
195
add a comment |
up vote
0
down vote
favorite
I want to select cell by cell in column "A" which are filled by colour red(colour code=3). Each time a button is clicked selection should move to next cell which is filled in red (same column).
My Code so far:
Sub FindNext()
Dim c As Range
On Error Resume Next
With Worksheets(1).Range("A1:B500")
Set c = .Find(Cells.Interior.ColorIndex = 3, LookIn:=xlValues)
If Not c Is Nothing Then
Do
c.Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
excel vba excel-vba
edited Nov 12 at 8:27
Pᴇʜ
20.1k42650
asked Nov 12 at 7:35
Saj W
195
2
Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 12 at 7:58
I have posted below the code I have done so far
– Saj W
Nov 12 at 8:17
Note: Never useOn Error Resume Nextwithout proper error handling. This line just hides all error messages but the errors still occur, you just cannot see them. Therefore you cannot fix them. Read VBA Error Handling – A Complete Guide to learn how do implement error handling.
– Pᴇʜ
Nov 12 at 8:29
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I want to select cell by cell in column "A" which are filled by colour red(colour code=3). Each time a button is clicked selection should move to next cell which is filled in red (same column).
My Code so far:
Sub FindNext()
Dim c As Range
On Error Resume Next
With Worksheets(1).Range("A1:B500")
Set c = .Find(Cells.Interior.ColorIndex = 3, LookIn:=xlValues)
If Not c Is Nothing Then
Do
c.Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
excel vba excel-vba
edited Nov 12 at 8:27
Pᴇʜ
20.1k42650
asked Nov 12 at 7:35
Saj W
195
I want to select cell by cell in column "A" which are filled by colour red(colour code=3). Each time a button is clicked selection should move to next cell which is filled in red (same column).
My Code so far:
Sub FindNext()
Dim c As Range
On Error Resume Next
With Worksheets(1).Range("A1:B500")
Set c = .Find(Cells.Interior.ColorIndex = 3, LookIn:=xlValues)
If Not c Is Nothing Then
Do
c.Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
excel vba excel-vba
excel vba excel-vba
edited Nov 12 at 8:27
Pᴇʜ
20.1k42650
asked Nov 12 at 7:35
Saj W
195
edited Nov 12 at 8:27
Pᴇʜ
20.1k42650
asked Nov 12 at 7:35
Saj W
195
edited Nov 12 at 8:27
Pᴇʜ
20.1k42650
edited Nov 12 at 8:27
Pᴇʜ
20.1k42650
edited Nov 12 at 8:27
Pᴇʜ
20.1k42650
20.1k42650
asked Nov 12 at 7:35
Saj W
195
asked Nov 12 at 7:35
Saj W
195
asked Nov 12 at 7:35
Saj W
195
195
2
Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 12 at 7:58
I have posted below the code I have done so far
– Saj W
Nov 12 at 8:17
Note: Never useOn Error Resume Nextwithout proper error handling. This line just hides all error messages but the errors still occur, you just cannot see them. Therefore you cannot fix them. Read VBA Error Handling – A Complete Guide to learn how do implement error handling.
– Pᴇʜ
Nov 12 at 8:29
add a comment |
2
Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 12 at 7:58
I have posted below the code I have done so far
– Saj W
Nov 12 at 8:17
Note: Never useOn Error Resume Nextwithout proper error handling. This line just hides all error messages but the errors still occur, you just cannot see them. Therefore you cannot fix them. Read VBA Error Handling – A Complete Guide to learn how do implement error handling.
– Pᴇʜ
Nov 12 at 8:29
2
2
Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 12 at 7:58
Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 12 at 7:58
I have posted below the code I have done so far
– Saj W
Nov 12 at 8:17
I have posted below the code I have done so far
– Saj W
Nov 12 at 8:17
Note: Never use
On Error Resume Next without proper error handling. This line just hides all error messages but the errors still occur, you just cannot see them. Therefore you cannot fix them. Read VBA Error Handling – A Complete Guide to learn how do implement error handling.– Pᴇʜ
Nov 12 at 8:29
Note: Never use
On Error Resume Next without proper error handling. This line just hides all error messages but the errors still occur, you just cannot see them. Therefore you cannot fix them. Read VBA Error Handling – A Complete Guide to learn how do implement error handling.– Pᴇʜ
Nov 12 at 8:29
add a comment |
2 Answers
2
active
oldest
votes
up vote
2
down vote
accepted
First the code for the red color is 255. If you mean another code, just change in the code below.
With a simple for loop you can get the next Red cell in the column:
Option Explicit
Sub test()
Dim Srow As Long
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Srow = Selection.Row
For i = Srow +1 To 500 'Replace 500 by Lastrow if your last cell has a value inside
If Cells(i, 1).Interior.Color = 255 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub
answered Nov 12 at 8:20
Pierre44
1,2841421
It should beFor i = Srow To LastRowI think?
– Pᴇʜ
Nov 12 at 8:38
1
I wrote that but then I noticed that OP wrote 500 for the last row, and that I don t know whether there are values everywhere or not. If he only has colors my 'Lastrow' doesn t work and I'd need to adjust it
– Pierre44
Nov 12 at 8:40
Ah nevermind, just was wondering why you determined the last row and then didn't use it.
– Pᴇʜ
Nov 12 at 8:47
Thanks for replies, It moves to the next red cell if the active cell is not red. Great.
– Saj W
Nov 12 at 8:55
I changed the Starting cell of the loop to the one after the current selection. With this it should select the next red cell.
– Pierre44
Nov 12 at 8:57
|
show 2 more comments
up vote
0
down vote
Sub Test()
Dim r As Range
If Intersect(ActiveCell, Columns("A")) Is Nothing Then [A1].Select
With Application.FindFormat
.clear
.Interior.ColorIndex = 3
End With
Set r = Columns("A").Find("", ActiveCell, searchformat:=True)
If Not r Is Nothing Then r.Select
End Sub
Another working solution
answered Nov 12 at 9:06
Saj W
195
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53257629%2fselect-next-value-that-meets-a-criteria%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
First the code for the red color is 255. If you mean another code, just change in the code below.
With a simple for loop you can get the next Red cell in the column:
Option Explicit
Sub test()
Dim Srow As Long
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Srow = Selection.Row
For i = Srow +1 To 500 'Replace 500 by Lastrow if your last cell has a value inside
If Cells(i, 1).Interior.Color = 255 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub
answered Nov 12 at 8:20
Pierre44
1,2841421
It should beFor i = Srow To LastRowI think?
– Pᴇʜ
Nov 12 at 8:38
1
I wrote that but then I noticed that OP wrote 500 for the last row, and that I don t know whether there are values everywhere or not. If he only has colors my 'Lastrow' doesn t work and I'd need to adjust it
– Pierre44
Nov 12 at 8:40
Ah nevermind, just was wondering why you determined the last row and then didn't use it.
– Pᴇʜ
Nov 12 at 8:47
Thanks for replies, It moves to the next red cell if the active cell is not red. Great.
– Saj W
Nov 12 at 8:55
I changed the Starting cell of the loop to the one after the current selection. With this it should select the next red cell.
– Pierre44
Nov 12 at 8:57
|
show 2 more comments
up vote
2
down vote
accepted
First the code for the red color is 255. If you mean another code, just change in the code below.
With a simple for loop you can get the next Red cell in the column:
Option Explicit
Sub test()
Dim Srow As Long
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Srow = Selection.Row
For i = Srow +1 To 500 'Replace 500 by Lastrow if your last cell has a value inside
If Cells(i, 1).Interior.Color = 255 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub
answered Nov 12 at 8:20
Pierre44
1,2841421
It should beFor i = Srow To LastRowI think?
– Pᴇʜ
Nov 12 at 8:38
1
I wrote that but then I noticed that OP wrote 500 for the last row, and that I don t know whether there are values everywhere or not. If he only has colors my 'Lastrow' doesn t work and I'd need to adjust it
– Pierre44
Nov 12 at 8:40
Ah nevermind, just was wondering why you determined the last row and then didn't use it.
– Pᴇʜ
Nov 12 at 8:47
Thanks for replies, It moves to the next red cell if the active cell is not red. Great.
– Saj W
Nov 12 at 8:55
I changed the Starting cell of the loop to the one after the current selection. With this it should select the next red cell.
– Pierre44
Nov 12 at 8:57
|
show 2 more comments
up vote
2
down vote
accepted
up vote
2
down vote
accepted
First the code for the red color is 255. If you mean another code, just change in the code below.
With a simple for loop you can get the next Red cell in the column:
Option Explicit
Sub test()
Dim Srow As Long
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Srow = Selection.Row
For i = Srow +1 To 500 'Replace 500 by Lastrow if your last cell has a value inside
If Cells(i, 1).Interior.Color = 255 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub
answered Nov 12 at 8:20
Pierre44
1,2841421
First the code for the red color is 255. If you mean another code, just change in the code below.
With a simple for loop you can get the next Red cell in the column:
Option Explicit
Sub test()
Dim Srow As Long
Dim LastRow As Long
Dim i As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Srow = Selection.Row
For i = Srow +1 To 500 'Replace 500 by Lastrow if your last cell has a value inside
If Cells(i, 1).Interior.Color = 255 Then
Cells(i, 1).Select
Exit Sub
End If
Next i
End Sub
answered Nov 12 at 8:20
Pierre44
1,2841421
edited Nov 12 at 8:56
answered Nov 12 at 8:20
Pierre44
1,2841421
answered Nov 12 at 8:20
Pierre44
1,2841421
answered Nov 12 at 8:20
Pierre44
1,2841421
1,2841421
It should beFor i = Srow To LastRowI think?
– Pᴇʜ
Nov 12 at 8:38
1
I wrote that but then I noticed that OP wrote 500 for the last row, and that I don t know whether there are values everywhere or not. If he only has colors my 'Lastrow' doesn t work and I'd need to adjust it
– Pierre44
Nov 12 at 8:40
Ah nevermind, just was wondering why you determined the last row and then didn't use it.
– Pᴇʜ
Nov 12 at 8:47
Thanks for replies, It moves to the next red cell if the active cell is not red. Great.
– Saj W
Nov 12 at 8:55
I changed the Starting cell of the loop to the one after the current selection. With this it should select the next red cell.
– Pierre44
Nov 12 at 8:57
|
show 2 more comments
It should beFor i = Srow To LastRowI think?
– Pᴇʜ
Nov 12 at 8:38
1
I wrote that but then I noticed that OP wrote 500 for the last row, and that I don t know whether there are values everywhere or not. If he only has colors my 'Lastrow' doesn t work and I'd need to adjust it
– Pierre44
Nov 12 at 8:40
Ah nevermind, just was wondering why you determined the last row and then didn't use it.
– Pᴇʜ
Nov 12 at 8:47
Thanks for replies, It moves to the next red cell if the active cell is not red. Great.
– Saj W
Nov 12 at 8:55
I changed the Starting cell of the loop to the one after the current selection. With this it should select the next red cell.
– Pierre44
Nov 12 at 8:57
It should be
For i = Srow To LastRow I think?– Pᴇʜ
Nov 12 at 8:38
It should be
For i = Srow To LastRow I think?– Pᴇʜ
Nov 12 at 8:38
1
1
I wrote that but then I noticed that OP wrote 500 for the last row, and that I don t know whether there are values everywhere or not. If he only has colors my 'Lastrow' doesn t work and I'd need to adjust it
– Pierre44
Nov 12 at 8:40
I wrote that but then I noticed that OP wrote 500 for the last row, and that I don t know whether there are values everywhere or not. If he only has colors my 'Lastrow' doesn t work and I'd need to adjust it
– Pierre44
Nov 12 at 8:40
Ah nevermind, just was wondering why you determined the last row and then didn't use it.
– Pᴇʜ
Nov 12 at 8:47
Ah nevermind, just was wondering why you determined the last row and then didn't use it.
– Pᴇʜ
Nov 12 at 8:47
Thanks for replies, It moves to the next red cell if the active cell is not red. Great.
– Saj W
Nov 12 at 8:55
Thanks for replies, It moves to the next red cell if the active cell is not red. Great.
– Saj W
Nov 12 at 8:55
I changed the Starting cell of the loop to the one after the current selection. With this it should select the next red cell.
– Pierre44
Nov 12 at 8:57
I changed the Starting cell of the loop to the one after the current selection. With this it should select the next red cell.
– Pierre44
Nov 12 at 8:57
|
show 2 more comments
up vote
0
down vote
Sub Test()
Dim r As Range
If Intersect(ActiveCell, Columns("A")) Is Nothing Then [A1].Select
With Application.FindFormat
.clear
.Interior.ColorIndex = 3
End With
Set r = Columns("A").Find("", ActiveCell, searchformat:=True)
If Not r Is Nothing Then r.Select
End Sub
Another working solution
answered Nov 12 at 9:06
Saj W
195
add a comment |
up vote
0
down vote
Sub Test()
Dim r As Range
If Intersect(ActiveCell, Columns("A")) Is Nothing Then [A1].Select
With Application.FindFormat
.clear
.Interior.ColorIndex = 3
End With
Set r = Columns("A").Find("", ActiveCell, searchformat:=True)
If Not r Is Nothing Then r.Select
End Sub
Another working solution
answered Nov 12 at 9:06
Saj W
195
add a comment |
up vote
0
down vote
up vote
0
down vote
Sub Test()
Dim r As Range
If Intersect(ActiveCell, Columns("A")) Is Nothing Then [A1].Select
With Application.FindFormat
.clear
.Interior.ColorIndex = 3
End With
Set r = Columns("A").Find("", ActiveCell, searchformat:=True)
If Not r Is Nothing Then r.Select
End Sub
Another working solution
answered Nov 12 at 9:06
Saj W
195
Sub Test()
Dim r As Range
If Intersect(ActiveCell, Columns("A")) Is Nothing Then [A1].Select
With Application.FindFormat
.clear
.Interior.ColorIndex = 3
End With
Set r = Columns("A").Find("", ActiveCell, searchformat:=True)
If Not r Is Nothing Then r.Select
End Sub
Another working solution
answered Nov 12 at 9:06
Saj W
195
answered Nov 12 at 9:06
Saj W
195
answered Nov 12 at 9:06
Saj W
195
answered Nov 12 at 9:06
Saj W
195
195
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53257629%2fselect-next-value-that-meets-a-criteria%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Welcome to Stack Overflow. Please note that because this is no free code writing service it is necessary to show either what you have tried so far and where you got stuck or errors (by showing your code) or at least to show what you have researched and the effort you made. Otherwise it is just asking us to do all the work for you. Reading How to Ask might help you to improve your question.
– Pᴇʜ
Nov 12 at 7:58
I have posted below the code I have done so far
– Saj W
Nov 12 at 8:17
Note: Never use
On Error Resume Nextwithout proper error handling. This line just hides all error messages but the errors still occur, you just cannot see them. Therefore you cannot fix them. Read VBA Error Handling – A Complete Guide to learn how do implement error handling.– Pᴇʜ
Nov 12 at 8:29