select from range

[Trevor3007]

good day,


For Each rng In Range("h4:h200")
Select Case rng.Value
Case "Build Completed"
With Range("A" & rng.Row).Resize(1, 22)
.Interior.ColorIndex = 4
.Font.Bold = True


the code highlighted in red is a triggered word. However, its a rather long winded way to change the word should the end user want to?
Is it possible to use a cell ref to do this?

"Build Completed" is part of drop down wihch is in the worktab name "Lists!A3" .

Many thanks in advance & KR
Trevor3007

 

[Norie]

What you could is set up a small table with the terms you want to check for, e.g. 'Build Completed', 'Swapped-out' etc., in one column and the corresponding colour index in a second column.

With such a table you could use a lookup or index/match to find the colour for each of the terms and apply it to the relevant range.

Actually, you are kind of half way there with the range you are using for the data validation.

 

[Trevor3007]

What you could is set up a small table with the terms you want to check for, e.g. 'Build Completed', 'Swapped-out' etc., in one column and the corresponding colour index in a second column.

With such a table you could use a lookup or index/match to find the colour for each of the terms and apply it to the relevant range.

Actually, you are kind of half way there with the range you are using for the data validation.

Hi Norrie,

Thanks for your reply. So are you saying you cannot do what I originally requested?


KR
Trevor3007

 

[Norie]

It's not possible to do what you want using Select...Case.

What I'm suggesting is a different method that would involve less code and would avoid having to hard-code values.

 

[Trevor3007]

It's not possible to do what you want using Select...Case.

What I'm suggesting is a different method that would involve less code and would avoid having to hard-code values.

OK & thanks...

I was just hoping to simplify for the EU.... could you amend & send me the results?

 

[Trevor3007]

The reason my script did not work was because I based it on only 7 lines of code you provide.
And you never mentioned it was a sheet change event script which I now see in one of your later postings.

I will move on and see what Norie can do for you.

morning,

Norrie states that it cannot be done ....are you able to sort a simple alternative ?

KR
Trevor3007

 

[My Aswer Is This]

I may be able to help.
But when I look back at post 1 I see no explanation of what your attempting to do.

I believe a lot of people find code on line which they think may work and then post it on this forum and want us to use this code but just modify it to my needs.

I would prefer to know what your wanting to achieve and then write you code to meet your needs.

Would you please explain what your needs are. Please do not say look at my file or read the script I have here. I never open files or click on links.

 

[My Aswer Is This]

And you said:

However, its a rather long winded way to change the word should the end user want to?

If you want the end user to be able to change the words without changing the code.
You would need to have a Inputbox popup asking you questions or have a list of values in a Listbox to choose from or something like this.

You tell me how would you like the end user to supply data to the script.
​

 

[Norie]

What I'm suggesting would mean there would be no hard-coded values in the code.

 

[Trevor3007]

And you said:

However, its a rather long winded way to change the word should the end user want to?

If you want the end user to be able to change the words without changing the code.
You would need to have a Inputbox popup asking you questions or have a list of values in a Listbox to choose from or something like this.

You tell me how would you like the end user to supply data to the script.
​

Hi

Sorry for any confusion or misleading, I will try my best to explain.

This workbook will be used on various projects\departments. The EU at some point may\will need to change what is already held in the 'list' sheet, A2>, (this includes the 'blank field') this is then used on the the 'daily data' sheet range h4-h200.

Currently if any value is changed \ added etc within the validation ('list' sheet ), it would also need to be amended in the code to reflect this change too. So rather then have to amend the VBA, I was hoping just to be able to do the change from a cell reference ?


Hope this covers & helps?

KR
Trevor3007

 

[Norie]

The method I'm suggesting would allow the user to alter the terms/phrases/colours etc without having to change the code.

PS I don't mean the existing code - like I said it's not possible to do what you want using a Select...Case structure.