Excel, VBA, Conditional Formatting












-2















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









share|improve this question




















  • 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















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









share|improve this question




















  • 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








-2


1






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









share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 26 '18 at 21:37







mtiger1

















asked Nov 19 '18 at 15:44









mtiger1mtiger1

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














  • 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












2 Answers
2






active

oldest

votes


















0














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






share|improve this answer


























  • 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



















0














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.






share|improve this answer

























    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


















    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









    0














    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






    share|improve this answer


























    • 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
















    0














    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






    share|improve this answer


























    • 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














    0












    0








    0







    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






    share|improve this answer















    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







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 20 '18 at 7:04

























    answered Nov 19 '18 at 16:48









    SINAN NIZARSINAN 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



















    • 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













    0














    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.






    share|improve this answer






























      0














      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.






      share|improve this answer




























        0












        0








        0







        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.






        share|improve this answer















        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.







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 28 '18 at 22:02

























        answered Nov 26 '18 at 16:50









        mtiger1mtiger1

        53




        53






























            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














            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





















































            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







            Popular posts from this blog

            How to change which sound is reproduced for terminal bell?

            Title Spacing in Bjornstrup Chapter, Removing Chapter Number From Contents

            Can I use Tabulator js library in my java Spring + Thymeleaf project?