Conditional Formatting? VBA?

[AMANDAJELLIOTT]

Hi Everyone,

First time poster here, and man do I need some help.

I am working on re-creating an "Activity Workbook" for my team to use at work. The original Workbook was created some years ago by someone who no longer works for my company, and it is in desperate need of updating. No one on my team has felt "Excel savvy" enough to take on the task until I joined the team. I soon realized that MAYBE, quit possibly, this could also be a bit over my head. So please, bare with me as I try to eloquently explain what I've gotten myself into.

For reference, I work in Human Resources, specifically dealing with employee benefits and leaves of absences. When someone is newly hired on, or changes positions, with my company there is a list of tasks that my team needs to perform for that new hire, such as collecting specific insurance enrollment documents, sending out insurance benefit letters and inputting their allotted sick leave hours in our HR management system. This Workbook acts as a check off list for these tasks, along with many others. Within the original Workbook is a sheet dedicated to a list of Actions that my team needs to perform based on whatever event is happening for that certain employee. There is also a master sheet where we can input employee information and select the action from a dropdown list which prompts some cells to be formatted (grey if we do not need to perform that action and color coded if we do). I have had to update the list of Actions and tasks associated with them, as they were very outdated or inaccurate. Now that I have done so, I do not know how to get the conditional formatting back in place that will automatically grey out the cells to the correlating task that does NOT need to be performed. I also don't know if CF is the best way to go about this. I have added some Snippit's of the Workbook to hopefully help my explanation.

Any help is much appreciated!!

 

[johnnyL]

@AMANDAJELLIOTT Welcome to mrexcel!

I don't think there is an easy fix for this.

Since it appears that there is no sensitive information displayed, can you upload that file somewhere so we have access to it? I often use this link to upload files to temporarily, but any place is fine as long as you provide the link to the file here.

In the 'Action List' sheet, already completed by you, are the actions that should be performed all updated and the actions that do not need to be performed already greyed out?

You are inquiring how to get the 'Master' sheet to reflect those greyed out selections when a particular 'action' is selected?

 

[Eric W]

If I have your sheets right, I might have an easy-ish way to do this. One problem is that an Excel formula (including Conditional Formatting formulas) cannot see cell formatting. But if you use a letter instead in the Actions_Table, like this:

Book2
	A	B	C	D	E	F
1
2
3
4		Actions	Task1	Task2	Task3	Task4
5		***Enrollments***
6		A	G	G
7		B	G		G	G
8		C
9		D	G			G
10		***CHANGE IN HOURS***
11		E	G	G	G	G
12		F
13		G	G		G
14		***CHANGE IN CLASSIFICATION***
15		H	G
16		I	G
17		J	G
Actions_Table

It's a partial section because I had to manually type it in, but I believe the rows/columns match. Then you can use a pretty simple formula on the MASTER sheet like this:

Book2
	E	F	G	H	I
1	Actions to perform	Task1	Task2	Task3	Task4
2	A
3	C
4	E
5	J
6
MASTER

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F:I	Expression	=INDEX(Actions_Table!C$6:C$100,MATCH($E1,Actions_Table!$B$6:$B$100,0))="G"	text	NO

 

[johnnyL]

@Eric W That is actually similar to what I was contemplating. Instead of 'G' though, I was thinking of possibly 'X' or 'NO'. Similar idea though. I guess you chose 'G' for Grey?

 

[AMANDAJELLIOTT]

@AMANDAJELLIOTT Welcome to mrexcel!

I don't think there is an easy fix for this.

Since it appears that there is no sensitive information displayed, can you upload that file somewhere so we have access to it? I often use this link to upload files to temporarily, but any place is fine as long as you provide the link to the file here.

In the 'Action List' sheet, already completed by you, are the actions that should be performed all updated and the actions that do not need to be performed already greyed out?

You are inquiring how to get the 'Master' sheet to reflect those greyed out selections when a particular 'action' is selected?

Thank you!!

I was afraid it would be complicated. I've used the site you recommended and have uploaded the file here. Gofile - Free file sharing and storage platform

I was in the middle of trying the "X" in the box method, to see if that would work. So now, in my actions_list, the actions that DO NOT need to be performed show an "X". When I select an action from the drop down list on the Master sheet, i need the boxes that correlate with the X to grey out, leaving only what needs to be done visible.

 

[johnnyL]

@AMANDAJELLIOTT The updated version of your file is here <---.

Thanks to @Eric W for the concept formula that was a few steps ahead of my thinking, but we were headed to the same objective in the end.

Let us know if that works for you.

 

[AMANDAJELLIOTT]

@AMANDAJELLIOTT The updated version of your file is here <---.

Thanks to @Eric W for the concept formula that was a few steps ahead of my thinking, but we were headed to the same objective in the end.

Let us know if that works for you.

WOW. I cannot thank you enough!! This is absolutely amazing.

 

[Eric W]

I guess you chose 'G' for Grey?

Yep, I had a vague idea of adding other rules if desired, R for red, B for blue, etc. Not really needed in this case.

Definitely some nice teamwork here. Amanda, I'm very glad we could help you out!

 

[AMANDAJELLIOTT]

Yep, I had a vague idea of adding other rules if desired, R for red, B for blue, etc. Not really needed in this case.

Definitely some nice teamwork here. Amanda, I'm very glad we could help you out!

Absolutely great teamwork, for sure! Thank you both so much!!!