Its not working the way I intended

[MGumbrell]

Please find attached a file that I hope is self explanatory. Details are on the worksheet.

I am getting unexpected results and I am sure that there is probably an easier way of writing what I am trying to achieve. My issue is explaining clearly what I am trying to achieve. There are hundreds of rows to add once the issues are solved. So I am hoping for an easier method of writing the VBA and assistance in understanding what is going wrong.

Regards, Matt

 

[ajetrumpet]

I think what you need is someone who knows something about mechanics and not someone who knows how to program. I have (or maybe someone else does though) no knowledge of working electricity, so I won't be of any help to you, and most likely noone else will either...

 

[Brianwarnock]

You don't say what is going wrong or what is the relevence of the yellow versus the white background comments. I did notice that those with a white background seemed irrelevent.

You also do not give any clue as to whether there is any rhyme or reason to which cells get highlighted.
As it stands I might consider a Select Case on Target.address, remember to quote absolute, $f$6 , but not sure that gains much.

Brian

 

[MGumbrell]

Guys as always its not the quality of the response that’s an issue but the quality of the question raised. I think that I may have a better way of asking the question.

1. The User selects a single Cell in Range E6:I47 (this range will increase)

2. Specific cell(s) in Range J6:Q47 need to be assigned to the selected cell so that the InteriorColor changes when the cell is selected by the user.

3. Each cell selected in Range E6:I47 will produce different results ie different cell(s) will highlight in Range J6:Q47

4. There may need to be an error notice should more than one cell in Range E6:I47 be selected. The user should only be able to select one cell at a time in this range.

5. The spreadsheet is a Matrix that visually links cells. The value of the cells are notes and technically not required. The spreadsheet is purely visual information of link cells.

I hope that I have made my question clearer for you guys. What I did find is that If Esle programming with the amount of rows I am eventually going to have will make the programme very slow. I suppose I am looking for a dynamic programme?

Regards, Matt

 

[Brianwarnock]

What I did find is that If Esle programming with the amount of rows I am eventually going to have will make the programme very slow. I suppose I am looking for a dynamic programme?

Regards, Matt

You did not mention or respond to my suggestion of using Select Case, I suspect but don't know if it is faster, but the Case Else would be triggered by an incorrect selection, including multicell.

As for a dynamic program, then that was my point about no rhyme or reason for the particular cells , if there is no , hmm a senior moment, ok I will say if there is no way to generate the cell references then select case or If then else seems to be the only way. i did briefly contemplate a lookup table but it didn't get passed the "I wonder if" stage.

Perhaps one of the more experienced or younger wizz kids can come up with something.

Brian

 

[MGumbrell]

Thank you Brian

My only concern is that I haven't use CASE before so nows as good a time as any to start.

I will still hold out for anyone with a dynamic approach too.

Thanks for your help.

Matt

 

[Brianwarnock]

Some ideas attached.

As for dynamic , how? What's the alogorithem?
How do you know what cells a given Cell highlights?

Brian

 

[MGumbrell]

I would have to programme the linked cells.

The worksheet for the user is purely for visual information once its been populated by me. (well with lots of help)

I am having a look at your attachement now.

Thanks, Matt

 

[MGumbrell]

Thank you Brian

I have changed a couple of things and it works the way that I wanted it too. I just have a lot of Case's to add.

I have attached the file with the changes for information.

Once again thanks for your help.

Regards, Matt

 

[Brianwarnock]

You have removed the error check for multi cell selection.

I would check the null at the beginning in the way that I checked for spare, leaving the Case Else as an error check.

Brian