Highlighting Cells Using Selected Header Cell & Dynamic Changes

CJWolford

New Member
Joined
Oct 29, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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

1698628623988.png
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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:​

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

3. Put the following code in the events of your sheet.​
VBA Code:
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
----- --
 
Upvote 0

Forum statistics

Threads
1,224,813
Messages
6,181,116
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top