Mixing
Excel, VBA, Conditional Formatting
I am needing some help. I believe VBA is the only way to solve this issue but I may be wrong.
On Sheet1 in my workbook I have two columns that contain different items. For instance in column B and column F contain different equipment items with a blank next to them for a quantity. It serves as a check list. Also on Sheet1 towards the top of the page are some conditional formatted check boxes that are selected for various "boxes" (ex: Box 1, Box 2, Box 3, etc.)
On Sheet2 there are different tables named for each box as mentioned above, and in the tables are different items. These items may or may not be the same items in col B & F on Sheet1.
Purpose: I am hoping to learn to write code that says when selecting the conditional formatted check boxes next to the various boxes on Sheet 1, then it will highlight the items on sheet1 if it matches any item in the selected Box from Sheet2.
****I have updated my question with the below code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' In order to run code on sheet without a button or enabling in a module
Set KeyCells = Range("A2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is
Nothing Then
' Display a message when one of the designated cells has been changed.
Dim i, j As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to
check the boxes
Dim d As Range
Sheets(1).Range("B11:B30, F11:F30").Font.ColorIndex = 0 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(2, 1) 'This refers to the checkbox - **QUESTION:How to have "multiple" check boxes to select from and will run the same code?**
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i))
For Each d In Sheets(1).Range("B11:B30, F11:F30")
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Font.ColorIndex = 3 'changes matching item to red font
End If
Next d
Next c
End If
Next i
End If
End Sub
excel vba excel-vba
asked Nov 19 '18 at 15:44
mtiger1
53
add a comment |
I am needing some help. I believe VBA is the only way to solve this issue but I may be wrong.
On Sheet1 in my workbook I have two columns that contain different items. For instance in column B and column F contain different equipment items with a blank next to them for a quantity. It serves as a check list. Also on Sheet1 towards the top of the page are some conditional formatted check boxes that are selected for various "boxes" (ex: Box 1, Box 2, Box 3, etc.)
On Sheet2 there are different tables named for each box as mentioned above, and in the tables are different items. These items may or may not be the same items in col B & F on Sheet1.
Purpose: I am hoping to learn to write code that says when selecting the conditional formatted check boxes next to the various boxes on Sheet 1, then it will highlight the items on sheet1 if it matches any item in the selected Box from Sheet2.
****I have updated my question with the below code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' In order to run code on sheet without a button or enabling in a module
Set KeyCells = Range("A2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is
Nothing Then
' Display a message when one of the designated cells has been changed.
Dim i, j As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to
check the boxes
Dim d As Range
Sheets(1).Range("B11:B30, F11:F30").Font.ColorIndex = 0 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(2, 1) 'This refers to the checkbox - **QUESTION:How to have "multiple" check boxes to select from and will run the same code?**
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i))
For Each d In Sheets(1).Range("B11:B30, F11:F30")
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Font.ColorIndex = 3 'changes matching item to red font
End If
Next d
Next c
End If
Next i
End If
End Sub
excel vba excel-vba
asked Nov 19 '18 at 15:44
mtiger1
53
2
I see you're new here. I would expect people to ask what you've tried and to show your attempts, which they're more inclined to help you with. Folks are always willing to help you understand what you're doing wrong, but they're often not willing to work to spec... from scratch.
– Marc
Nov 19 '18 at 16:05
I've inserted some code above but it is not ideal. The problem is that I'm only seeing conditional format options but I know that is not what I need.
– mtiger1
Nov 19 '18 at 16:12
add a comment |
I am needing some help. I believe VBA is the only way to solve this issue but I may be wrong.
On Sheet1 in my workbook I have two columns that contain different items. For instance in column B and column F contain different equipment items with a blank next to them for a quantity. It serves as a check list. Also on Sheet1 towards the top of the page are some conditional formatted check boxes that are selected for various "boxes" (ex: Box 1, Box 2, Box 3, etc.)
On Sheet2 there are different tables named for each box as mentioned above, and in the tables are different items. These items may or may not be the same items in col B & F on Sheet1.
Purpose: I am hoping to learn to write code that says when selecting the conditional formatted check boxes next to the various boxes on Sheet 1, then it will highlight the items on sheet1 if it matches any item in the selected Box from Sheet2.
****I have updated my question with the below code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' In order to run code on sheet without a button or enabling in a module
Set KeyCells = Range("A2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is
Nothing Then
' Display a message when one of the designated cells has been changed.
Dim i, j As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to
check the boxes
Dim d As Range
Sheets(1).Range("B11:B30, F11:F30").Font.ColorIndex = 0 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(2, 1) 'This refers to the checkbox - **QUESTION:How to have "multiple" check boxes to select from and will run the same code?**
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i))
For Each d In Sheets(1).Range("B11:B30, F11:F30")
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Font.ColorIndex = 3 'changes matching item to red font
End If
Next d
Next c
End If
Next i
End If
End Sub
excel vba excel-vba
asked Nov 19 '18 at 15:44
mtiger1
53
I am needing some help. I believe VBA is the only way to solve this issue but I may be wrong.
On Sheet1 in my workbook I have two columns that contain different items. For instance in column B and column F contain different equipment items with a blank next to them for a quantity. It serves as a check list. Also on Sheet1 towards the top of the page are some conditional formatted check boxes that are selected for various "boxes" (ex: Box 1, Box 2, Box 3, etc.)
On Sheet2 there are different tables named for each box as mentioned above, and in the tables are different items. These items may or may not be the same items in col B & F on Sheet1.
Purpose: I am hoping to learn to write code that says when selecting the conditional formatted check boxes next to the various boxes on Sheet 1, then it will highlight the items on sheet1 if it matches any item in the selected Box from Sheet2.
****I have updated my question with the below code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' In order to run code on sheet without a button or enabling in a module
Set KeyCells = Range("A2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is
Nothing Then
' Display a message when one of the designated cells has been changed.
Dim i, j As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to
check the boxes
Dim d As Range
Sheets(1).Range("B11:B30, F11:F30").Font.ColorIndex = 0 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(2, 1) 'This refers to the checkbox - **QUESTION:How to have "multiple" check boxes to select from and will run the same code?**
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i))
For Each d In Sheets(1).Range("B11:B30, F11:F30")
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Font.ColorIndex = 3 'changes matching item to red font
End If
Next d
Next c
End If
Next i
End If
End Sub
excel vba excel-vba
excel vba excel-vba
asked Nov 19 '18 at 15:44
mtiger1
53
asked Nov 19 '18 at 15:44
mtiger1
53
edited Nov 26 '18 at 21:37
asked Nov 19 '18 at 15:44
mtiger1
53
asked Nov 19 '18 at 15:44
mtiger1
53
asked Nov 19 '18 at 15:44
mtiger1
53
53
2
I see you're new here. I would expect people to ask what you've tried and to show your attempts, which they're more inclined to help you with. Folks are always willing to help you understand what you're doing wrong, but they're often not willing to work to spec... from scratch.
– Marc
Nov 19 '18 at 16:05
I've inserted some code above but it is not ideal. The problem is that I'm only seeing conditional format options but I know that is not what I need.
– mtiger1
Nov 19 '18 at 16:12
add a comment |
2
I see you're new here. I would expect people to ask what you've tried and to show your attempts, which they're more inclined to help you with. Folks are always willing to help you understand what you're doing wrong, but they're often not willing to work to spec... from scratch.
– Marc
Nov 19 '18 at 16:05
I've inserted some code above but it is not ideal. The problem is that I'm only seeing conditional format options but I know that is not what I need.
– mtiger1
Nov 19 '18 at 16:12
2
2
I see you're new here. I would expect people to ask what you've tried and to show your attempts, which they're more inclined to help you with. Folks are always willing to help you understand what you're doing wrong, but they're often not willing to work to spec... from scratch.
– Marc
Nov 19 '18 at 16:05
I see you're new here. I would expect people to ask what you've tried and to show your attempts, which they're more inclined to help you with. Folks are always willing to help you understand what you're doing wrong, but they're often not willing to work to spec... from scratch.
– Marc
Nov 19 '18 at 16:05
I've inserted some code above but it is not ideal. The problem is that I'm only seeing conditional format options but I know that is not what I need.
– mtiger1
Nov 19 '18 at 16:12
I've inserted some code above but it is not ideal. The problem is that I'm only seeing conditional format options but I know that is not what I need.
– mtiger1
Nov 19 '18 at 16:12
add a comment |
2 Answers
2
active
oldest
votes
Based on what I understood from your question I have written a code to format cell color. I have given the comments along with the code.
Sub format()
Dim i As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range(Cells(4, 1), Cells(50, 50)).Interior.ColorIndex = xlNone 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(1, 1) 'This refers to the checkbox
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(20, i))
For Each d In Sheets(1).Range(Cells(4, 2), Cells(21, 21))
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Interior.ColorIndex = 6 'If true give yellow colour
End If
Next d
Next c
End If
Next i
End Sub
And this is the excel sheet that I used Excel Sheet
answered Nov 19 '18 at 16:48
SINAN NIZAR
334
Looks like I'm unable to access the excel sheet but I will give this a try and let you know... Thanks!
– mtiger1
Nov 19 '18 at 18:25
Try the Excel Sheet link now. I have activated it.
– SINAN NIZAR
Nov 20 '18 at 1:11
If it works please accept my answer.
– SINAN NIZAR
Nov 21 '18 at 1:14
Is there a way to have multiple check boxes. For instance, have A1, A2, and A3 on sheet 1 do the same. I was trying to figure this out but I keep getting an error
– mtiger1
Nov 21 '18 at 15:14
Could you provide more info or provide an excel sheet as you want or atleast a screen shot of the sheets..
– SINAN NIZAR
Nov 22 '18 at 1:09
|
show 2 more comments
Please see below for code that I have so far. It is a variation of what I received above. I have posted the question on line 13.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' In order to run code on sheet without a button or enabling in a module
Set KeyCells = Range("A2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is
Nothing Then
' Display a message when one of the designated cells has been changed.
Dim i, j As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range("B11:B30, F11:F30").Font.ColorIndex = 0 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(2, 1) 'This refers to the checkbox - **QUESTION: How to have "multiple" check boxes to select from and will run the same code?**
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i))
For Each d In Sheets(1).Range("B11:B30, F11:F30")
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Font.ColorIndex = 3 'changes matching item to red font
End If
Next d
Next c
End If
Next i
End If
End Sub
Sheet 1 Check boxes - I have conditional formatted check boxes which i can change. They were based on a data validation list of 0,1 to change filling. But I may have to change this.
I have answered my own question. Basically you can just have multiple strings box1, box2, etc. and write the same code for each string. Not sure if this is the long way of doing it but it works.
answered Nov 26 '18 at 16:50
mtiger1
53
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%2f53378150%2fexcel-vba-conditional-formatting%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
Based on what I understood from your question I have written a code to format cell color. I have given the comments along with the code.
Sub format()
Dim i As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range(Cells(4, 1), Cells(50, 50)).Interior.ColorIndex = xlNone 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(1, 1) 'This refers to the checkbox
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(20, i))
For Each d In Sheets(1).Range(Cells(4, 2), Cells(21, 21))
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Interior.ColorIndex = 6 'If true give yellow colour
End If
Next d
Next c
End If
Next i
End Sub
And this is the excel sheet that I used Excel Sheet
answered Nov 19 '18 at 16:48
SINAN NIZAR
334
Looks like I'm unable to access the excel sheet but I will give this a try and let you know... Thanks!
– mtiger1
Nov 19 '18 at 18:25
Try the Excel Sheet link now. I have activated it.
– SINAN NIZAR
Nov 20 '18 at 1:11
If it works please accept my answer.
– SINAN NIZAR
Nov 21 '18 at 1:14
Is there a way to have multiple check boxes. For instance, have A1, A2, and A3 on sheet 1 do the same. I was trying to figure this out but I keep getting an error
– mtiger1
Nov 21 '18 at 15:14
Could you provide more info or provide an excel sheet as you want or atleast a screen shot of the sheets..
– SINAN NIZAR
Nov 22 '18 at 1:09
|
show 2 more comments
Based on what I understood from your question I have written a code to format cell color. I have given the comments along with the code.
Sub format()
Dim i As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range(Cells(4, 1), Cells(50, 50)).Interior.ColorIndex = xlNone 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(1, 1) 'This refers to the checkbox
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(20, i))
For Each d In Sheets(1).Range(Cells(4, 2), Cells(21, 21))
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Interior.ColorIndex = 6 'If true give yellow colour
End If
Next d
Next c
End If
Next i
End Sub
And this is the excel sheet that I used Excel Sheet
answered Nov 19 '18 at 16:48
SINAN NIZAR
334
Looks like I'm unable to access the excel sheet but I will give this a try and let you know... Thanks!
– mtiger1
Nov 19 '18 at 18:25
Try the Excel Sheet link now. I have activated it.
– SINAN NIZAR
Nov 20 '18 at 1:11
If it works please accept my answer.
– SINAN NIZAR
Nov 21 '18 at 1:14
Is there a way to have multiple check boxes. For instance, have A1, A2, and A3 on sheet 1 do the same. I was trying to figure this out but I keep getting an error
– mtiger1
Nov 21 '18 at 15:14
Could you provide more info or provide an excel sheet as you want or atleast a screen shot of the sheets..
– SINAN NIZAR
Nov 22 '18 at 1:09
|
show 2 more comments
Based on what I understood from your question I have written a code to format cell color. I have given the comments along with the code.
Sub format()
Dim i As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range(Cells(4, 1), Cells(50, 50)).Interior.ColorIndex = xlNone 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(1, 1) 'This refers to the checkbox
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(20, i))
For Each d In Sheets(1).Range(Cells(4, 2), Cells(21, 21))
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Interior.ColorIndex = 6 'If true give yellow colour
End If
Next d
Next c
End If
Next i
End Sub
And this is the excel sheet that I used Excel Sheet
answered Nov 19 '18 at 16:48
SINAN NIZAR
334
Based on what I understood from your question I have written a code to format cell color. I have given the comments along with the code.
Sub format()
Dim i As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range(Cells(4, 1), Cells(50, 50)).Interior.ColorIndex = xlNone 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(1, 1) 'This refers to the checkbox
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(20, i))
For Each d In Sheets(1).Range(Cells(4, 2), Cells(21, 21))
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Interior.ColorIndex = 6 'If true give yellow colour
End If
Next d
Next c
End If
Next i
End Sub
And this is the excel sheet that I used Excel Sheet
answered Nov 19 '18 at 16:48
SINAN NIZAR
334
edited Nov 20 '18 at 7:04
answered Nov 19 '18 at 16:48
SINAN NIZAR
334
answered Nov 19 '18 at 16:48
SINAN NIZAR
334
answered Nov 19 '18 at 16:48
SINAN NIZAR
334
334
Looks like I'm unable to access the excel sheet but I will give this a try and let you know... Thanks!
– mtiger1
Nov 19 '18 at 18:25
Try the Excel Sheet link now. I have activated it.
– SINAN NIZAR
Nov 20 '18 at 1:11
If it works please accept my answer.
– SINAN NIZAR
Nov 21 '18 at 1:14
Is there a way to have multiple check boxes. For instance, have A1, A2, and A3 on sheet 1 do the same. I was trying to figure this out but I keep getting an error
– mtiger1
Nov 21 '18 at 15:14
Could you provide more info or provide an excel sheet as you want or atleast a screen shot of the sheets..
– SINAN NIZAR
Nov 22 '18 at 1:09
|
show 2 more comments
Looks like I'm unable to access the excel sheet but I will give this a try and let you know... Thanks!
– mtiger1
Nov 19 '18 at 18:25
Try the Excel Sheet link now. I have activated it.
– SINAN NIZAR
Nov 20 '18 at 1:11
If it works please accept my answer.
– SINAN NIZAR
Nov 21 '18 at 1:14
Is there a way to have multiple check boxes. For instance, have A1, A2, and A3 on sheet 1 do the same. I was trying to figure this out but I keep getting an error
– mtiger1
Nov 21 '18 at 15:14
Could you provide more info or provide an excel sheet as you want or atleast a screen shot of the sheets..
– SINAN NIZAR
Nov 22 '18 at 1:09
Looks like I'm unable to access the excel sheet but I will give this a try and let you know... Thanks!
– mtiger1
Nov 19 '18 at 18:25
Looks like I'm unable to access the excel sheet but I will give this a try and let you know... Thanks!
– mtiger1
Nov 19 '18 at 18:25
Try the Excel Sheet link now. I have activated it.
– SINAN NIZAR
Nov 20 '18 at 1:11
Try the Excel Sheet link now. I have activated it.
– SINAN NIZAR
Nov 20 '18 at 1:11
If it works please accept my answer.
– SINAN NIZAR
Nov 21 '18 at 1:14
If it works please accept my answer.
– SINAN NIZAR
Nov 21 '18 at 1:14
Is there a way to have multiple check boxes. For instance, have A1, A2, and A3 on sheet 1 do the same. I was trying to figure this out but I keep getting an error
– mtiger1
Nov 21 '18 at 15:14
Is there a way to have multiple check boxes. For instance, have A1, A2, and A3 on sheet 1 do the same. I was trying to figure this out but I keep getting an error
– mtiger1
Nov 21 '18 at 15:14
Could you provide more info or provide an excel sheet as you want or atleast a screen shot of the sheets..
– SINAN NIZAR
Nov 22 '18 at 1:09
Could you provide more info or provide an excel sheet as you want or atleast a screen shot of the sheets..
– SINAN NIZAR
Nov 22 '18 at 1:09
|
show 2 more comments
Please see below for code that I have so far. It is a variation of what I received above. I have posted the question on line 13.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' In order to run code on sheet without a button or enabling in a module
Set KeyCells = Range("A2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is
Nothing Then
' Display a message when one of the designated cells has been changed.
Dim i, j As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range("B11:B30, F11:F30").Font.ColorIndex = 0 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(2, 1) 'This refers to the checkbox - **QUESTION: How to have "multiple" check boxes to select from and will run the same code?**
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i))
For Each d In Sheets(1).Range("B11:B30, F11:F30")
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Font.ColorIndex = 3 'changes matching item to red font
End If
Next d
Next c
End If
Next i
End If
End Sub
Sheet 1 Check boxes - I have conditional formatted check boxes which i can change. They were based on a data validation list of 0,1 to change filling. But I may have to change this.
I have answered my own question. Basically you can just have multiple strings box1, box2, etc. and write the same code for each string. Not sure if this is the long way of doing it but it works.
answered Nov 26 '18 at 16:50
mtiger1
53
add a comment |
Please see below for code that I have so far. It is a variation of what I received above. I have posted the question on line 13.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' In order to run code on sheet without a button or enabling in a module
Set KeyCells = Range("A2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is
Nothing Then
' Display a message when one of the designated cells has been changed.
Dim i, j As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range("B11:B30, F11:F30").Font.ColorIndex = 0 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(2, 1) 'This refers to the checkbox - **QUESTION: How to have "multiple" check boxes to select from and will run the same code?**
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i))
For Each d In Sheets(1).Range("B11:B30, F11:F30")
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Font.ColorIndex = 3 'changes matching item to red font
End If
Next d
Next c
End If
Next i
End If
End Sub
Sheet 1 Check boxes - I have conditional formatted check boxes which i can change. They were based on a data validation list of 0,1 to change filling. But I may have to change this.
I have answered my own question. Basically you can just have multiple strings box1, box2, etc. and write the same code for each string. Not sure if this is the long way of doing it but it works.
answered Nov 26 '18 at 16:50
mtiger1
53
add a comment |
Please see below for code that I have so far. It is a variation of what I received above. I have posted the question on line 13.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' In order to run code on sheet without a button or enabling in a module
Set KeyCells = Range("A2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is
Nothing Then
' Display a message when one of the designated cells has been changed.
Dim i, j As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range("B11:B30, F11:F30").Font.ColorIndex = 0 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(2, 1) 'This refers to the checkbox - **QUESTION: How to have "multiple" check boxes to select from and will run the same code?**
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i))
For Each d In Sheets(1).Range("B11:B30, F11:F30")
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Font.ColorIndex = 3 'changes matching item to red font
End If
Next d
Next c
End If
Next i
End If
End Sub
Sheet 1 Check boxes - I have conditional formatted check boxes which i can change. They were based on a data validation list of 0,1 to change filling. But I may have to change this.
I have answered my own question. Basically you can just have multiple strings box1, box2, etc. and write the same code for each string. Not sure if this is the long way of doing it but it works.
answered Nov 26 '18 at 16:50
mtiger1
53
Please see below for code that I have so far. It is a variation of what I received above. I have posted the question on line 13.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' In order to run code on sheet without a button or enabling in a module
Set KeyCells = Range("A2")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is
Nothing Then
' Display a message when one of the designated cells has been changed.
Dim i, j As Integer
Dim box As String
Dim c As Range 'Define two ranges so that we can loop through both sheets to check the boxes
Dim d As Range
Sheets(1).Range("B11:B30, F11:F30").Font.ColorIndex = 0 'Remove the cell styles to apply new ones
box = Sheets(1).Cells(2, 1) 'This refers to the checkbox - **QUESTION: How to have "multiple" check boxes to select from and will run the same code?**
For i = 1 To 10 'Loop to find the checked box in sheet2
If Sheets(2).Cells(1, i) = box Then 'Check for checked box
For Each c In Sheets(2).Range(Sheets(2).Cells(2, i), Sheets(2).Cells(6, i))
For Each d In Sheets(1).Range("B11:B30, F11:F30")
If c = d Then
Sheets(1).Cells(d.Row, d.Column).Font.ColorIndex = 3 'changes matching item to red font
End If
Next d
Next c
End If
Next i
End If
End Sub
Sheet 1 Check boxes - I have conditional formatted check boxes which i can change. They were based on a data validation list of 0,1 to change filling. But I may have to change this.
I have answered my own question. Basically you can just have multiple strings box1, box2, etc. and write the same code for each string. Not sure if this is the long way of doing it but it works.
answered Nov 26 '18 at 16:50
mtiger1
53
edited Nov 28 '18 at 22:02
answered Nov 26 '18 at 16:50
mtiger1
53
answered Nov 26 '18 at 16:50
mtiger1
53
answered Nov 26 '18 at 16:50
mtiger1
53
53
add a comment |
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%2f53378150%2fexcel-vba-conditional-formatting%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
I see you're new here. I would expect people to ask what you've tried and to show your attempts, which they're more inclined to help you with. Folks are always willing to help you understand what you're doing wrong, but they're often not willing to work to spec... from scratch.
– Marc
Nov 19 '18 at 16:05
I've inserted some code above but it is not ideal. The problem is that I'm only seeing conditional format options but I know that is not what I need.
– mtiger1
Nov 19 '18 at 16:12