Temporary cell highlighting, reverting the original cell color
I want to make a macro that Highlights the selected rows but does not change the original cell color once the highlighting is over (once the cell is not selected anymore).
*(Original cell color : The color of the originally specified cell.)
and here is code that I used
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static xRow
If xRow <> "" Then
With Rows(xRow).Interior
.ColorIndex=Xlnone
End with
End If
pRow = Selection.Row
xRow = pRow
With Rows(pRow).Interior
.ColorIndex=6
.Pattern=XlSolid
End With
End Sub
but when I use code like this, Highlight is active, but the original cell color is changed.
How Can I fix it?
excel vba excel-vba highlight
edited Nov 20 '18 at 13:56
Pᴇʜ
20.2k42650
asked Nov 20 '18 at 9:09
Chae-Hee Kim
11
|
show 5 more comments
I want to make a macro that Highlights the selected rows but does not change the original cell color once the highlighting is over (once the cell is not selected anymore).
*(Original cell color : The color of the originally specified cell.)
and here is code that I used
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static xRow
If xRow <> "" Then
With Rows(xRow).Interior
.ColorIndex=Xlnone
End with
End If
pRow = Selection.Row
xRow = pRow
With Rows(pRow).Interior
.ColorIndex=6
.Pattern=XlSolid
End With
End Sub
but when I use code like this, Highlight is active, but the original cell color is changed.
How Can I fix it?
excel vba excel-vba highlight
edited Nov 20 '18 at 13:56
Pᴇʜ
20.2k42650
asked Nov 20 '18 at 9:09
Chae-Hee Kim
11
This.ColorIndex=X|noneis no valid syntax. It should bexlNoneandxlSolid. I recommend to activateOption Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration to avoid typos like that.
– Pᴇʜ
Nov 20 '18 at 9:17
ah, it is typo sorry.. it is not |, I want to wrote l (L) .. sorry
– Chae-Hee Kim
Nov 20 '18 at 9:20
1
I think you are looking for something like that: atlaspm.com/toms-tutorials-for-excel/… There are several tutorials on Google how to highlight cells, rows and columns. Use one of them so you don't need to invent it on your own.
– Pᴇʜ
Nov 20 '18 at 9:31
5
"I'm not a American so I'm not good at English" - That's ok, Americans aren't good at English either. :-P
– Gravitate
Nov 20 '18 at 10:49
If you want to preserve the original colour you have to store it somewhere, not erase it.
– Luuklag
Nov 20 '18 at 10:55
|
show 5 more comments
I want to make a macro that Highlights the selected rows but does not change the original cell color once the highlighting is over (once the cell is not selected anymore).
*(Original cell color : The color of the originally specified cell.)
and here is code that I used
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static xRow
If xRow <> "" Then
With Rows(xRow).Interior
.ColorIndex=Xlnone
End with
End If
pRow = Selection.Row
xRow = pRow
With Rows(pRow).Interior
.ColorIndex=6
.Pattern=XlSolid
End With
End Sub
but when I use code like this, Highlight is active, but the original cell color is changed.
How Can I fix it?
excel vba excel-vba highlight
edited Nov 20 '18 at 13:56
Pᴇʜ
20.2k42650
asked Nov 20 '18 at 9:09
Chae-Hee Kim
11
I want to make a macro that Highlights the selected rows but does not change the original cell color once the highlighting is over (once the cell is not selected anymore).
*(Original cell color : The color of the originally specified cell.)
and here is code that I used
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static xRow
If xRow <> "" Then
With Rows(xRow).Interior
.ColorIndex=Xlnone
End with
End If
pRow = Selection.Row
xRow = pRow
With Rows(pRow).Interior
.ColorIndex=6
.Pattern=XlSolid
End With
End Sub
but when I use code like this, Highlight is active, but the original cell color is changed.
How Can I fix it?
excel vba excel-vba highlight
excel vba excel-vba highlight
edited Nov 20 '18 at 13:56
Pᴇʜ
20.2k42650
asked Nov 20 '18 at 9:09
Chae-Hee Kim
11
edited Nov 20 '18 at 13:56
Pᴇʜ
20.2k42650
asked Nov 20 '18 at 9:09
Chae-Hee Kim
11
edited Nov 20 '18 at 13:56
Pᴇʜ
20.2k42650
edited Nov 20 '18 at 13:56
Pᴇʜ
20.2k42650
edited Nov 20 '18 at 13:56
Pᴇʜ
20.2k42650
20.2k42650
asked Nov 20 '18 at 9:09
Chae-Hee Kim
11
asked Nov 20 '18 at 9:09
Chae-Hee Kim
11
asked Nov 20 '18 at 9:09
Chae-Hee Kim
11
11
This.ColorIndex=X|noneis no valid syntax. It should bexlNoneandxlSolid. I recommend to activateOption Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration to avoid typos like that.
– Pᴇʜ
Nov 20 '18 at 9:17
ah, it is typo sorry.. it is not |, I want to wrote l (L) .. sorry
– Chae-Hee Kim
Nov 20 '18 at 9:20
1
I think you are looking for something like that: atlaspm.com/toms-tutorials-for-excel/… There are several tutorials on Google how to highlight cells, rows and columns. Use one of them so you don't need to invent it on your own.
– Pᴇʜ
Nov 20 '18 at 9:31
5
"I'm not a American so I'm not good at English" - That's ok, Americans aren't good at English either. :-P
– Gravitate
Nov 20 '18 at 10:49
If you want to preserve the original colour you have to store it somewhere, not erase it.
– Luuklag
Nov 20 '18 at 10:55
|
show 5 more comments
This.ColorIndex=X|noneis no valid syntax. It should bexlNoneandxlSolid. I recommend to activateOption Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration to avoid typos like that.
– Pᴇʜ
Nov 20 '18 at 9:17
ah, it is typo sorry.. it is not |, I want to wrote l (L) .. sorry
– Chae-Hee Kim
Nov 20 '18 at 9:20
1
I think you are looking for something like that: atlaspm.com/toms-tutorials-for-excel/… There are several tutorials on Google how to highlight cells, rows and columns. Use one of them so you don't need to invent it on your own.
– Pᴇʜ
Nov 20 '18 at 9:31
5
"I'm not a American so I'm not good at English" - That's ok, Americans aren't good at English either. :-P
– Gravitate
Nov 20 '18 at 10:49
If you want to preserve the original colour you have to store it somewhere, not erase it.
– Luuklag
Nov 20 '18 at 10:55
This
.ColorIndex=X|none is no valid syntax. It should be xlNone and xlSolid. I recommend to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration to avoid typos like that.– Pᴇʜ
Nov 20 '18 at 9:17
This
.ColorIndex=X|none is no valid syntax. It should be xlNone and xlSolid. I recommend to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration to avoid typos like that.– Pᴇʜ
Nov 20 '18 at 9:17
ah, it is typo sorry.. it is not |, I want to wrote l (L) .. sorry
– Chae-Hee Kim
Nov 20 '18 at 9:20
ah, it is typo sorry.. it is not |, I want to wrote l (L) .. sorry
– Chae-Hee Kim
Nov 20 '18 at 9:20
1
1
I think you are looking for something like that: atlaspm.com/toms-tutorials-for-excel/… There are several tutorials on Google how to highlight cells, rows and columns. Use one of them so you don't need to invent it on your own.
– Pᴇʜ
Nov 20 '18 at 9:31
I think you are looking for something like that: atlaspm.com/toms-tutorials-for-excel/… There are several tutorials on Google how to highlight cells, rows and columns. Use one of them so you don't need to invent it on your own.
– Pᴇʜ
Nov 20 '18 at 9:31
5
5
"I'm not a American so I'm not good at English" - That's ok, Americans aren't good at English either. :-P
– Gravitate
Nov 20 '18 at 10:49
"I'm not a American so I'm not good at English" - That's ok, Americans aren't good at English either. :-P
– Gravitate
Nov 20 '18 at 10:49
If you want to preserve the original colour you have to store it somewhere, not erase it.
– Luuklag
Nov 20 '18 at 10:55
If you want to preserve the original colour you have to store it somewhere, not erase it.
– Luuklag
Nov 20 '18 at 10:55
|
show 5 more comments
1 Answer
1
active
oldest
votes
Something that could work (did not test it) is to use conditional formatting to change the color and therefore let it always automatically restore the original color. Not sure about how good the performance of this workaround will be.
In a module add the following function:
Public Function IsSelected() As Boolean
IsSelected = Not Intersect(Application.Caller, Range("SelectedRange")) Is Nothing
End Function
In your desired worksheet add the following event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Names.Add "SelectedRange", Target
End Sub
In the desired range add a conditional formatting with the formula:
=IsSelected()
answered Nov 20 '18 at 13:34
Pᴇʜ
20.2k42650
really appreciate for your answer, I'll do it right now :D
– Chae-Hee Kim
Nov 21 '18 at 6:18
@Chae-HeeKim If it solved your question please mark it as solution: Accepting Answers: How does it work?
– Pᴇʜ
Nov 21 '18 at 6:57
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
});
}
});
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%2f53389586%2ftemporary-cell-highlighting-reverting-the-original-cell-color%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Something that could work (did not test it) is to use conditional formatting to change the color and therefore let it always automatically restore the original color. Not sure about how good the performance of this workaround will be.
In a module add the following function:
Public Function IsSelected() As Boolean
IsSelected = Not Intersect(Application.Caller, Range("SelectedRange")) Is Nothing
End Function
In your desired worksheet add the following event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Names.Add "SelectedRange", Target
End Sub
In the desired range add a conditional formatting with the formula:
=IsSelected()
answered Nov 20 '18 at 13:34
Pᴇʜ
20.2k42650
really appreciate for your answer, I'll do it right now :D
– Chae-Hee Kim
Nov 21 '18 at 6:18
@Chae-HeeKim If it solved your question please mark it as solution: Accepting Answers: How does it work?
– Pᴇʜ
Nov 21 '18 at 6:57
add a comment |
Something that could work (did not test it) is to use conditional formatting to change the color and therefore let it always automatically restore the original color. Not sure about how good the performance of this workaround will be.
In a module add the following function:
Public Function IsSelected() As Boolean
IsSelected = Not Intersect(Application.Caller, Range("SelectedRange")) Is Nothing
End Function
In your desired worksheet add the following event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Names.Add "SelectedRange", Target
End Sub
In the desired range add a conditional formatting with the formula:
=IsSelected()
answered Nov 20 '18 at 13:34
Pᴇʜ
20.2k42650
really appreciate for your answer, I'll do it right now :D
– Chae-Hee Kim
Nov 21 '18 at 6:18
@Chae-HeeKim If it solved your question please mark it as solution: Accepting Answers: How does it work?
– Pᴇʜ
Nov 21 '18 at 6:57
add a comment |
Something that could work (did not test it) is to use conditional formatting to change the color and therefore let it always automatically restore the original color. Not sure about how good the performance of this workaround will be.
In a module add the following function:
Public Function IsSelected() As Boolean
IsSelected = Not Intersect(Application.Caller, Range("SelectedRange")) Is Nothing
End Function
In your desired worksheet add the following event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Names.Add "SelectedRange", Target
End Sub
In the desired range add a conditional formatting with the formula:
=IsSelected()
answered Nov 20 '18 at 13:34
Pᴇʜ
20.2k42650
Something that could work (did not test it) is to use conditional formatting to change the color and therefore let it always automatically restore the original color. Not sure about how good the performance of this workaround will be.
In a module add the following function:
Public Function IsSelected() As Boolean
IsSelected = Not Intersect(Application.Caller, Range("SelectedRange")) Is Nothing
End Function
In your desired worksheet add the following event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Names.Add "SelectedRange", Target
End Sub
In the desired range add a conditional formatting with the formula:
=IsSelected()
answered Nov 20 '18 at 13:34
Pᴇʜ
20.2k42650
edited Nov 20 '18 at 14:11
answered Nov 20 '18 at 13:34
Pᴇʜ
20.2k42650
answered Nov 20 '18 at 13:34
Pᴇʜ
20.2k42650
answered Nov 20 '18 at 13:34
Pᴇʜ
20.2k42650
20.2k42650
really appreciate for your answer, I'll do it right now :D
– Chae-Hee Kim
Nov 21 '18 at 6:18
@Chae-HeeKim If it solved your question please mark it as solution: Accepting Answers: How does it work?
– Pᴇʜ
Nov 21 '18 at 6:57
add a comment |
really appreciate for your answer, I'll do it right now :D
– Chae-Hee Kim
Nov 21 '18 at 6:18
@Chae-HeeKim If it solved your question please mark it as solution: Accepting Answers: How does it work?
– Pᴇʜ
Nov 21 '18 at 6:57
really appreciate for your answer, I'll do it right now :D
– Chae-Hee Kim
Nov 21 '18 at 6:18
really appreciate for your answer, I'll do it right now :D
– Chae-Hee Kim
Nov 21 '18 at 6:18
@Chae-HeeKim If it solved your question please mark it as solution: Accepting Answers: How does it work?
– Pᴇʜ
Nov 21 '18 at 6:57
@Chae-HeeKim If it solved your question please mark it as solution: Accepting Answers: How does it work?
– Pᴇʜ
Nov 21 '18 at 6:57
add a comment |
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.
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%2f53389586%2ftemporary-cell-highlighting-reverting-the-original-cell-color%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
This
.ColorIndex=X|noneis no valid syntax. It should bexlNoneandxlSolid. I recommend to activateOption Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration to avoid typos like that.– Pᴇʜ
Nov 20 '18 at 9:17
ah, it is typo sorry.. it is not |, I want to wrote l (L) .. sorry
– Chae-Hee Kim
Nov 20 '18 at 9:20
1
I think you are looking for something like that: atlaspm.com/toms-tutorials-for-excel/… There are several tutorials on Google how to highlight cells, rows and columns. Use one of them so you don't need to invent it on your own.
– Pᴇʜ
Nov 20 '18 at 9:31
5
"I'm not a American so I'm not good at English" - That's ok, Americans aren't good at English either. :-P
– Gravitate
Nov 20 '18 at 10:49
If you want to preserve the original colour you have to store it somewhere, not erase it.
– Luuklag
Nov 20 '18 at 10:55