Retain color from different sheets
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have 20+ sheets with the same structure and I am trying to make a summarization on the first sheet. The issue I am facing is that I am a complete rookie when it comes to how to use vba.
The code below is what I thought was the first step, all the cells from A2:A31 in blad5 should retain the background color into blad1 E2:E31, if I change the color in blad5 it would show take color on blad1 (All the cells in Blad5 A2:A31 have a green background color)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(Blad2).Range("A2:A31").Interior.Color = Worksheets(Blad1).Range("E2:E31").Interior.Color
End Sub
My intention is to utilize a do loop over all the sheets (blad2-blad27) to blad1 which is my summarization sheet.
Would be grateful for a nudge in the right direction on how to do a function en do loop for the sheets (minus blad1 / sheet1) and how to properly write the interior.color macro.
With kind regards,
Fredrik
excel vba
edited Nov 23 '18 at 12:53
Karthick Gunasekaran
2,50611224
asked Nov 23 '18 at 12:21
FredrikFredrik
1
add a comment |
0
I have 20+ sheets with the same structure and I am trying to make a summarization on the first sheet. The issue I am facing is that I am a complete rookie when it comes to how to use vba.
The code below is what I thought was the first step, all the cells from A2:A31 in blad5 should retain the background color into blad1 E2:E31, if I change the color in blad5 it would show take color on blad1 (All the cells in Blad5 A2:A31 have a green background color)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(Blad2).Range("A2:A31").Interior.Color = Worksheets(Blad1).Range("E2:E31").Interior.Color
End Sub
My intention is to utilize a do loop over all the sheets (blad2-blad27) to blad1 which is my summarization sheet.
Would be grateful for a nudge in the right direction on how to do a function en do loop for the sheets (minus blad1 / sheet1) and how to properly write the interior.color macro.
With kind regards,
Fredrik
excel vba
edited Nov 23 '18 at 12:53
Karthick Gunasekaran
2,50611224
asked Nov 23 '18 at 12:21
FredrikFredrik
1
Do all the cells inWorksheets(Blad1).Range("E2:E31")have the sameInterior .Color?
– cybernetic.nomad
Nov 23 '18 at 12:28
@cybernetic.nomad the interior.color for blad1 E2:E31 are all white i.e no fill
– Fredrik
Nov 23 '18 at 12:40
add a comment |
0
0
0
I have 20+ sheets with the same structure and I am trying to make a summarization on the first sheet. The issue I am facing is that I am a complete rookie when it comes to how to use vba.
The code below is what I thought was the first step, all the cells from A2:A31 in blad5 should retain the background color into blad1 E2:E31, if I change the color in blad5 it would show take color on blad1 (All the cells in Blad5 A2:A31 have a green background color)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(Blad2).Range("A2:A31").Interior.Color = Worksheets(Blad1).Range("E2:E31").Interior.Color
End Sub
My intention is to utilize a do loop over all the sheets (blad2-blad27) to blad1 which is my summarization sheet.
Would be grateful for a nudge in the right direction on how to do a function en do loop for the sheets (minus blad1 / sheet1) and how to properly write the interior.color macro.
With kind regards,
Fredrik
excel vba
edited Nov 23 '18 at 12:53
Karthick Gunasekaran
2,50611224
asked Nov 23 '18 at 12:21
FredrikFredrik
1
I have 20+ sheets with the same structure and I am trying to make a summarization on the first sheet. The issue I am facing is that I am a complete rookie when it comes to how to use vba.
The code below is what I thought was the first step, all the cells from A2:A31 in blad5 should retain the background color into blad1 E2:E31, if I change the color in blad5 it would show take color on blad1 (All the cells in Blad5 A2:A31 have a green background color)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(Blad2).Range("A2:A31").Interior.Color = Worksheets(Blad1).Range("E2:E31").Interior.Color
End Sub
My intention is to utilize a do loop over all the sheets (blad2-blad27) to blad1 which is my summarization sheet.
Would be grateful for a nudge in the right direction on how to do a function en do loop for the sheets (minus blad1 / sheet1) and how to properly write the interior.color macro.
With kind regards,
Fredrik
excel vba
excel vba
edited Nov 23 '18 at 12:53
Karthick Gunasekaran
2,50611224
asked Nov 23 '18 at 12:21
FredrikFredrik
1
edited Nov 23 '18 at 12:53
Karthick Gunasekaran
2,50611224
asked Nov 23 '18 at 12:21
FredrikFredrik
1
edited Nov 23 '18 at 12:53
Karthick Gunasekaran
2,50611224
edited Nov 23 '18 at 12:53
Karthick Gunasekaran
2,50611224
edited Nov 23 '18 at 12:53
Karthick Gunasekaran
2,50611224
2,50611224
asked Nov 23 '18 at 12:21
FredrikFredrik
1
asked Nov 23 '18 at 12:21
FredrikFredrik
1
asked Nov 23 '18 at 12:21
FredrikFredrik
1
1
Do all the cells inWorksheets(Blad1).Range("E2:E31")have the sameInterior .Color?
– cybernetic.nomad
Nov 23 '18 at 12:28
@cybernetic.nomad the interior.color for blad1 E2:E31 are all white i.e no fill
– Fredrik
Nov 23 '18 at 12:40
add a comment |
Do all the cells inWorksheets(Blad1).Range("E2:E31")have the sameInterior .Color?
– cybernetic.nomad
Nov 23 '18 at 12:28
@cybernetic.nomad the interior.color for blad1 E2:E31 are all white i.e no fill
– Fredrik
Nov 23 '18 at 12:40
Do all the cells in
Worksheets(Blad1).Range("E2:E31") have the same Interior .Color?– cybernetic.nomad
Nov 23 '18 at 12:28
Do all the cells in
Worksheets(Blad1).Range("E2:E31") have the same Interior .Color?– cybernetic.nomad
Nov 23 '18 at 12:28
@cybernetic.nomad the interior.color for blad1 E2:E31 are all white i.e no fill
– Fredrik
Nov 23 '18 at 12:40
@cybernetic.nomad the interior.color for blad1 E2:E31 are all white i.e no fill
– Fredrik
Nov 23 '18 at 12:40
add a comment |
1 Answer
1
active
oldest
votes
0
For the loop over the sheets you can use following:
For i = 2 To 27
Worksheets("Blad" & i).Range("A2:A31").Interior.Color=Worksheets(Blad1).Range("E2:E31").Interior.Color
Next i
answered Nov 23 '18 at 13:13
Pavel ChmelíkPavel Chmelík
13
Hi, I am gettting a error 13 when trying to compile the macro.
– Fredrik
Nov 26 '18 at 7:47
How did you dim your variables?
– Pavel Chmelík
Nov 26 '18 at 8:32
I haven't, I am just using the default dims from the sheets. The thing I explained which was pretty lackluster is the my summarziation sheet (blad1) has columns which correspond to blad2-blad27. So the second part Interior.Color=Worksheets(Blad1).Range("E2:E31") has to reflect this aswell. One question, if I rename the sheets will excel vba still understand blad1 aso?
– Fredrik
Nov 26 '18 at 8:40
If you want to be renaming the sheets it is better than to use the VBA aliases in codes (it is the part that is shown in the VBA project window before the sheet name in the brackets. If you want to have all used cells colored on the sheet you can use ActiveRange property like here before you use the colors: ActiveSheet.UsedRange.Select. If you want to clor just part that changes you can try to find last row and column like here: stackoverflow.com/questions/38882321/…
– Pavel Chmelík
Nov 26 '18 at 14:55
And for the variables, set variables as a certain type at the beginning of the code like: Dim i As Integer. Error 13 type mismatch is a sign that the excel expects different datatype than the variable is used for.
– Pavel Chmelík
Nov 26 '18 at 14:59
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%2f53446644%2fretain-color-from-different-sheets%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
0
For the loop over the sheets you can use following:
For i = 2 To 27
Worksheets("Blad" & i).Range("A2:A31").Interior.Color=Worksheets(Blad1).Range("E2:E31").Interior.Color
Next i
answered Nov 23 '18 at 13:13
Pavel ChmelíkPavel Chmelík
13
Hi, I am gettting a error 13 when trying to compile the macro.
– Fredrik
Nov 26 '18 at 7:47
How did you dim your variables?
– Pavel Chmelík
Nov 26 '18 at 8:32
I haven't, I am just using the default dims from the sheets. The thing I explained which was pretty lackluster is the my summarziation sheet (blad1) has columns which correspond to blad2-blad27. So the second part Interior.Color=Worksheets(Blad1).Range("E2:E31") has to reflect this aswell. One question, if I rename the sheets will excel vba still understand blad1 aso?
– Fredrik
Nov 26 '18 at 8:40
If you want to be renaming the sheets it is better than to use the VBA aliases in codes (it is the part that is shown in the VBA project window before the sheet name in the brackets. If you want to have all used cells colored on the sheet you can use ActiveRange property like here before you use the colors: ActiveSheet.UsedRange.Select. If you want to clor just part that changes you can try to find last row and column like here: stackoverflow.com/questions/38882321/…
– Pavel Chmelík
Nov 26 '18 at 14:55
And for the variables, set variables as a certain type at the beginning of the code like: Dim i As Integer. Error 13 type mismatch is a sign that the excel expects different datatype than the variable is used for.
– Pavel Chmelík
Nov 26 '18 at 14:59
add a comment |
0
For the loop over the sheets you can use following:
For i = 2 To 27
Worksheets("Blad" & i).Range("A2:A31").Interior.Color=Worksheets(Blad1).Range("E2:E31").Interior.Color
Next i
answered Nov 23 '18 at 13:13
Pavel ChmelíkPavel Chmelík
13
Hi, I am gettting a error 13 when trying to compile the macro.
– Fredrik
Nov 26 '18 at 7:47
How did you dim your variables?
– Pavel Chmelík
Nov 26 '18 at 8:32
I haven't, I am just using the default dims from the sheets. The thing I explained which was pretty lackluster is the my summarziation sheet (blad1) has columns which correspond to blad2-blad27. So the second part Interior.Color=Worksheets(Blad1).Range("E2:E31") has to reflect this aswell. One question, if I rename the sheets will excel vba still understand blad1 aso?
– Fredrik
Nov 26 '18 at 8:40
If you want to be renaming the sheets it is better than to use the VBA aliases in codes (it is the part that is shown in the VBA project window before the sheet name in the brackets. If you want to have all used cells colored on the sheet you can use ActiveRange property like here before you use the colors: ActiveSheet.UsedRange.Select. If you want to clor just part that changes you can try to find last row and column like here: stackoverflow.com/questions/38882321/…
– Pavel Chmelík
Nov 26 '18 at 14:55
And for the variables, set variables as a certain type at the beginning of the code like: Dim i As Integer. Error 13 type mismatch is a sign that the excel expects different datatype than the variable is used for.
– Pavel Chmelík
Nov 26 '18 at 14:59
add a comment |
0
0
0
For the loop over the sheets you can use following:
For i = 2 To 27
Worksheets("Blad" & i).Range("A2:A31").Interior.Color=Worksheets(Blad1).Range("E2:E31").Interior.Color
Next i
answered Nov 23 '18 at 13:13
Pavel ChmelíkPavel Chmelík
13
For the loop over the sheets you can use following:
For i = 2 To 27
Worksheets("Blad" & i).Range("A2:A31").Interior.Color=Worksheets(Blad1).Range("E2:E31").Interior.Color
Next i
answered Nov 23 '18 at 13:13
Pavel ChmelíkPavel Chmelík
13
answered Nov 23 '18 at 13:13
Pavel ChmelíkPavel Chmelík
13
answered Nov 23 '18 at 13:13
Pavel ChmelíkPavel Chmelík
13
answered Nov 23 '18 at 13:13
Pavel ChmelíkPavel Chmelík
13
13
Hi, I am gettting a error 13 when trying to compile the macro.
– Fredrik
Nov 26 '18 at 7:47
How did you dim your variables?
– Pavel Chmelík
Nov 26 '18 at 8:32
I haven't, I am just using the default dims from the sheets. The thing I explained which was pretty lackluster is the my summarziation sheet (blad1) has columns which correspond to blad2-blad27. So the second part Interior.Color=Worksheets(Blad1).Range("E2:E31") has to reflect this aswell. One question, if I rename the sheets will excel vba still understand blad1 aso?
– Fredrik
Nov 26 '18 at 8:40
If you want to be renaming the sheets it is better than to use the VBA aliases in codes (it is the part that is shown in the VBA project window before the sheet name in the brackets. If you want to have all used cells colored on the sheet you can use ActiveRange property like here before you use the colors: ActiveSheet.UsedRange.Select. If you want to clor just part that changes you can try to find last row and column like here: stackoverflow.com/questions/38882321/…
– Pavel Chmelík
Nov 26 '18 at 14:55
And for the variables, set variables as a certain type at the beginning of the code like: Dim i As Integer. Error 13 type mismatch is a sign that the excel expects different datatype than the variable is used for.
– Pavel Chmelík
Nov 26 '18 at 14:59
add a comment |
Hi, I am gettting a error 13 when trying to compile the macro.
– Fredrik
Nov 26 '18 at 7:47
How did you dim your variables?
– Pavel Chmelík
Nov 26 '18 at 8:32
I haven't, I am just using the default dims from the sheets. The thing I explained which was pretty lackluster is the my summarziation sheet (blad1) has columns which correspond to blad2-blad27. So the second part Interior.Color=Worksheets(Blad1).Range("E2:E31") has to reflect this aswell. One question, if I rename the sheets will excel vba still understand blad1 aso?
– Fredrik
Nov 26 '18 at 8:40
If you want to be renaming the sheets it is better than to use the VBA aliases in codes (it is the part that is shown in the VBA project window before the sheet name in the brackets. If you want to have all used cells colored on the sheet you can use ActiveRange property like here before you use the colors: ActiveSheet.UsedRange.Select. If you want to clor just part that changes you can try to find last row and column like here: stackoverflow.com/questions/38882321/…
– Pavel Chmelík
Nov 26 '18 at 14:55
And for the variables, set variables as a certain type at the beginning of the code like: Dim i As Integer. Error 13 type mismatch is a sign that the excel expects different datatype than the variable is used for.
– Pavel Chmelík
Nov 26 '18 at 14:59
Hi, I am gettting a error 13 when trying to compile the macro.
– Fredrik
Nov 26 '18 at 7:47
Hi, I am gettting a error 13 when trying to compile the macro.
– Fredrik
Nov 26 '18 at 7:47
How did you dim your variables?
– Pavel Chmelík
Nov 26 '18 at 8:32
How did you dim your variables?
– Pavel Chmelík
Nov 26 '18 at 8:32
I haven't, I am just using the default dims from the sheets. The thing I explained which was pretty lackluster is the my summarziation sheet (blad1) has columns which correspond to blad2-blad27. So the second part Interior.Color=Worksheets(Blad1).Range("E2:E31") has to reflect this aswell. One question, if I rename the sheets will excel vba still understand blad1 aso?
– Fredrik
Nov 26 '18 at 8:40
I haven't, I am just using the default dims from the sheets. The thing I explained which was pretty lackluster is the my summarziation sheet (blad1) has columns which correspond to blad2-blad27. So the second part Interior.Color=Worksheets(Blad1).Range("E2:E31") has to reflect this aswell. One question, if I rename the sheets will excel vba still understand blad1 aso?
– Fredrik
Nov 26 '18 at 8:40
If you want to be renaming the sheets it is better than to use the VBA aliases in codes (it is the part that is shown in the VBA project window before the sheet name in the brackets. If you want to have all used cells colored on the sheet you can use ActiveRange property like here before you use the colors: ActiveSheet.UsedRange.Select. If you want to clor just part that changes you can try to find last row and column like here: stackoverflow.com/questions/38882321/…
– Pavel Chmelík
Nov 26 '18 at 14:55
If you want to be renaming the sheets it is better than to use the VBA aliases in codes (it is the part that is shown in the VBA project window before the sheet name in the brackets. If you want to have all used cells colored on the sheet you can use ActiveRange property like here before you use the colors: ActiveSheet.UsedRange.Select. If you want to clor just part that changes you can try to find last row and column like here: stackoverflow.com/questions/38882321/…
– Pavel Chmelík
Nov 26 '18 at 14:55
And for the variables, set variables as a certain type at the beginning of the code like: Dim i As Integer. Error 13 type mismatch is a sign that the excel expects different datatype than the variable is used for.
– Pavel Chmelík
Nov 26 '18 at 14:59
And for the variables, set variables as a certain type at the beginning of the code like: Dim i As Integer. Error 13 type mismatch is a sign that the excel expects different datatype than the variable is used for.
– Pavel Chmelík
Nov 26 '18 at 14:59
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.
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%2f53446644%2fretain-color-from-different-sheets%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
Do all the cells in
Worksheets(Blad1).Range("E2:E31")have the sameInterior .Color?– cybernetic.nomad
Nov 23 '18 at 12:28
@cybernetic.nomad the interior.color for blad1 E2:E31 are all white i.e no fill
– Fredrik
Nov 23 '18 at 12:40