VBA to highlight certain rows

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
401
Office Version
  1. 2010
Platform
  1. Windows
Hello. I am looking for a way that when a certain button is clicked it will highlight certain rows. See pics for better explanation...

Before screenshot:


Then upon clicking "production" button:


And when clicking on "shipping" button:



Thank you for all and any help!

Keith
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,967
Office Version
  1. 2013
Platform
  1. Windows
I would like for you to explain what rows you want hidden when you click a button and not just expect us to look at an image to know what you want.
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
401
Office Version
  1. 2010
Platform
  1. Windows
ok... sorry about that. :oops:

THe spreadsheet that I inherited here is a training matrix where the employees are listed at the top and the procedures that they are to be trained on are listed in rows. The problem is is that there are multiple rows with the same procedure listed several times... I want to simplify it and only have 1 procedure listed one time. The reason why they are listed multiple times is that some procedures are used in multiple departments (and thus listed multiple times on the spreadsheet.) an example would be Procedure QA-003 GF... is used in both Shipping and Production. What I am aiming to do is remove the department headers (*as shown in rows 6 and 17) and list all the procedures one time, and then when the appropiate button is clicked, (say, for shipping) the procedures associated with shipping will be highlighted yellow. (and I would need another button to 'clear' the highlighted rows, or maybe when the button is selected again it would clear the formatting... not sure how that would work and what would be the easiest way to accomplish this.) Hope all that makes sense and I didnt confuse you even more... lol THANK YOU! :)
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,967
Office Version
  1. 2013
Platform
  1. Windows
I do not see any thing in these images that would tell me what procedures John Doe needs to be trained on.
I see one cell with a "X" does that mean John Doe only needs to be trained on one procedure

And if that is the case why are all the rows highlighted?

Is that the object of this script to highlight what rows John Doe needs to be trained on?
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
401
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I do not see any thing in these images that would tell me what procedures John Doe needs to be trained on.
I see one cell with a "X" does that mean John Doe only needs to be trained on one procedure

And if that is the case why are all the rows highlighted?

Is that the object of this script to highlight what rows John Doe needs to be trained on?

Yea, what I did was just cut alot of the content out for the purpose of posting it here... The rows that would ultimately be highlighted would each have an "X" in the smaller(narrower) cell side in the column that includes their name.

Here is what I am thinking... this is after I have eliminated the duplicate procedures from all the rows... this would be for the Production employees (upon clicking on the Production button:)


THis would be for the Shipping employees:
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
401
Office Version
  1. 2010
Platform
  1. Windows
Is that the object of this script to highlight what rows John Doe needs to be trained on?

No, I already have the script for that part completed and it works great… What ive hidden from the above screen shots is a column that shows the date the procedure was either created, or the latest modified date if it has been edited. Beside each of the cells that contain an “X”, is a cell that contains a date that each employee was trained for that procedure. I have the cell conditionally formatted so that if the training date is < the procedure creation/edited date, then the cell will turn red. The button I have that says Show/Hide Dates toggles between hiding every-other-cell to show that actual date of when the employee signed off on the training and show just the “X” (so its not as “busy” looking with all the dates showing.)
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
401
Office Version
  1. 2010
Platform
  1. Windows
Here is the actual spreadsheet i am working on showing the X's representing training has been competed, and RED indicating that the procedure has been edited since the training was completed and thus needs to be trained again. Thanks again!

 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
401
Office Version
  1. 2010
Platform
  1. Windows
Also, replying specifically to your comment:

I do not see any thing in these images that would tell me what procedures John Doe needs to be trained on.

Here is what the existing code executes when you hit the shippng button. It shows only employees that are dedicated as shipping (I have a hidden row right below the names in row 1 that specifies what deparetment the employee works in. Shipping is "S". So when the script is ran, it shows only employees with S in row 2 and hides all the others. )

Showing Shipping only: (the procedures (rows) that would be highlighted would only be for shipping specific procedures.)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,991
Messages
5,856,689
Members
431,828
Latest member
kARTIK12345

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
Top