Highlighting Cells Using Selected Header Cell & Dynamic Changes

[CJWolford]

Hello All,

I'm working on a training matrix and have the setup, but I need to add some ease-of-use functionality. However, my functionality wish far surpasses my Excel knowledge and ability.

1. Across the top of the matrix (columns H-BC) are the various areas an employee could be trained. Column A displays the master list of document numbers. Column B holds those document number's title. If the training curriculum references a document, an "x" is placed in the corresponding row and column. What I would like to happen is when I click on the a header cell, the document numbers and titles referenced by that training curriculum become highlighted so it's very easy to see which documents are referenced instead of looking for "x"s in a cell.

2. I would like to make the "Matrix" sheet dynamic. I have created a "Document" sheet that acts as the master list. If the document list is changed on the "Document" sheet, I want the "Matrix" sheet's Columns A and B to change automatically. I would also like to make separate sheets for each training area (columns H-BC) that lists only the documents referenced by their individual curriculum. If a document is listed on this sheet, it is matched with the document number and title on "Matrix" and the "x"s are put where they need to be.

I have a feeling this is going to involve a v- or xlookup function (which I'm not to great with) along with conditional formatting (which, if it's not a basic formula in conditional formatting, I get thoroughly confused), and possibly some VBA (all of my VBA knowledge deals with Access). I've attached a picture of what I've set up so far (no confidential info is shared, so no worries there.) Any and all help is appreciated. And if more information is needed, please ask!

Thank you.
CJ

 

[DanteAmor]

Hi @CJWolford, Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

I'll help you with point number 1:

What I would like to happen is when I click on the a header cell, the document numbers and titles referenced by that training curriculum become highlighted

Use conditional formatting. Create a new rule.

1. Select the range that you want to make this work, Starting in cell A6 to B1000 for example.​

2. Create a new conditional formatting rule, select "Use a formula to determine which cells to format", enter the following formula:​

=AND($A6=INDIRECT(CELL("address")),INDIRECT(CELL("address"))<>"",CELL("row")=3)

3. Put the following code in the events of your sheet.​

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

SHEET EVENT

Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.​

4. For it to work you must select a cell in row 3 from columns H-BC​

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --