Agfdhyk

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

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

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.