Can anyone help with this problem and point me in the right direction please.

[lee ashton]

Hi all,

I need help with either vba code or formula.

I have a data validation drop down list which contains numbers 1-45.

I am able to select multiple numbers from the drop down list at once.

Look at the middle column. Below RUN No is my drop list. Below this are columns with various numbers and letters. The numbers I am interested in are the black font size 18 (23 for example).

When I select 23 from my drop list I want excel to do the following things.

1. create a border containing the cell with 23 in, the cell to the left, and the block of 4 cells below ending up with a block of cells 2 x 3 inside a border. 

2. highlight the cells within the border.

3. it is very important that the selected cells remain like this when other cells are being selected.

Can anyone help me and point me in the right direction.

many thanks

Lee

[#2 · Accepted solution]

I would like to have a try, but, your sample doesn't show as what you want. 

For example, point 1, after selecting 23, the 4 cells (2x2) below it are not highlighted.   I guess it would look like "18" in your screenshot, but only 2x2cells (including the number row) are highlighted, not 2x3.

Also, would you please clarify

1. If those "RUN NO" are selected, do you mean all the cells contains the "RUN NO"s will be highlighted; or just the cells below that RUN NO will be highlighted?
   What I am asking is, for example, if I select "18" on the "RUN NO" above "29 BACK", should we highlight the "18" which is below "29 OUT"?
2. This is about your point 3 "it is very important that the selected cells remain like this when other cells are being selected."  If we selected "23" this time, and then, change the selection to "24", should the formatting in the cells around 23 be removed?

[#3]

Hi Willy Lau,

Thank you for your response.

The cells highlighted in yellow have not been selected, but are highlighted for another reason.

If Run No 18 was selected it would have to be shaded in red with 75% grey. It would also be the same as I mentioned, a block of 6 cells 2 wide by 3 high.

Point 1 that you mentioned, only the Run No's selected would be highlighted and have to remain highlighted when other Run No's are selected. This is because other Run No's may be selected at a later stage by the user.

Point 2 that you mentioned, the Data Validation list has the ability to have multiple selections made at once. Therefore I could choose Run No 23 on its own or 23 and 24 together.

The Data Validation selection refers to the numbers in the column directly below.

I hope this clarifies things a little better for you.

And once again many thanks for your response I have been trying to get help with this problem for months now.

Regards

Lee Ashton