Custom Highlighting: Click one cell, highlight others

[AndrewH]

Hello all,


I have been struggling with figuring out the best way to accomplish some conditional formatting and am hoping one of you kind folks would have an answer for me.


I am creating a very rudimentary sheet that lists several projects and when testing will be occuring (see example below). Below that I have a list of analysts assigned to the projects - each analyst may be on one or more projects, and each project will have multiple analysts.


My goal is to highlight the names of the analysts when a specific project is selected, and I just can't get it to work.


Any help would be greatly appreciated.


Project1

Project2

Project3

Project4

Project5

Project6

Project7

Project8


Analyst1

Analyst2

Analyst3

Analyst4

Analyst5

Analyst6

Analyst7

Analyst8

Analyst9

Analyst10

Analyst11

Analyst12

Analyst13

Analyst14

Analyst15

Analyst16

Analyst17

Analyst18

Analyst19

 

[Montrey]

Well first of all, you have to have somewhere else what analyst are with what projects in some form or fashion. Then you can set up a match formula.


Excel won't be able to read your mind.

 

[AndrewH]

Good point Montrey. On a second tab I was planning on having this listed, but was waiting for a suggestion on grouping. Thanks for catching that!

 

[shrivallabha]

Hi Andrew,


It may not be necessary to use VBA based Click if you choose an option like:

1. Create a project list

2. Refer this list via data validation to one cell e.g. A1

3. On a separate sheet, Build project-wise analysts' list and name those ranges using names used in Step 1.

4. Apply conditional formatting formula like:

=ISNUMBER(MATCH(Rng_To_Check,INDIRECT(DV_Cell_Project_List),0))


I am attaching a sample for clarity:

https://www.box.com/s/ei42abxi6hyk1vblbybt

 

[AndrewH]

Apologies for not getting back to this sooner...

shrivallabha - thank you very much! Unfortunately, I need to be able to show all projects and analysts at the same time, as well as the data in the columns to the right. An ideal solution would be to highlight the analysts associated with a project when clicking on the specific project. I now have the projects listed in separate columns on tab2 with analysts assigned underneath (see below). Thoughts?


Project1 Project2 etc...

Analyst1 Analyst1

Analyst3 Analyst4

 

[SirJB7]

Hi, AndrewH!


Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.


Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!

 

[Debraj]

Hi, AndrewH!

I would also like to have a view of your sample data..


for the time being can you please check the attach file, if it acceptable..


If it is in the right path, then I can try to make it more usable..


https://dl.dropbox.com/u/78831150/Excel/Custom%20Highlighting%20Click%20one%20cell%2C%20highlight%20others%28AndrewH%29.xlsm


* I have used a VBA code for autocalucate..

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then Calculate
End Sub[/pre]
You can reduce it as per your requirement.. and No more VBA.. :)


* I have used a helper column to match the selected data

=ISNUMBER(MATCH($C$2:$C$21,OFFSET(H1,1,MATCH(CELL("contents"),$H$1:$N$1,0)-1,12),0))

* I have passed the same to highlight the matched data..


Regards,

Deb..


PS: When you are permitted to run the MACRO just for single line, I hope you will allow us to go for full VBA, no conditional Formatting.. otherwise, you have to press F9 after seelcting the source data..

 

[AndrewH]

Once again all, sorry for not responding for some time...


Sir and Debraj, I will upload the data in one moment, as it should help illustrate my problem.


Debraj, I have one question on your example, as it may work; however, I don't understand utilizing =RANDBETWEEN, as it changes the analysts assigned to a project each time I click a different project.


Thanks again!

 

[AndrewH]

Actually, it looks like I cannot upload a sheet as we cannot access file sharing sites here at work. I can try when I am at home, just not here...

 

[AndrewH]

Debraj, thank you! I figured it out!! Thank you all for your help!!

 

[SirJB7]

Hi, AndrewH!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: If you figured it out different from what posted above, maybe you want to post your solution so as people who read this might know how to do it. Thank you.