change a cell colour via a diffrent cell

[garethtaylor]

I have a drop down list of eight items on sheet 2 when ever I change the drop down list the colour changes (thanks for the help with adding more than 3 conditional formatting)
On sheet 1 I have a number in a cell the number will always be seven but what I am wanting to do is when I change the list on sheet 2 the colour changes in the box on sheet 1 in that cell with the seven in it.

Is this possible and if so how. Just to note this will be replicated around 300 times on the sheet.

So the easier the better.


Thank you.

[garethtaylor]

Thought it might help if i include the sheet.
please look at the "front sheet tab" cell D13 for what I am trying to do

thank you

[garethtaylor]

does this mean it is not possible? if so i will try to do it a diffrent way.

[Palmetto]

Here is the code:

Note: For brevity in the post I am only showing the additional code which DOES NOT include your Select Case code. Code shown in red is new dimensions. All of your Select Case code goes where the comment is shown

Please Login or Register  to view this content.

[garethtaylor]

Hi, I am really grateful for your reply but I am also really thick as I don’t really understand it, pasted the code you wrote above and added what I thought I had to add, I tried changing the column and row references, I left them as they were, well I have tried for a good solid 2 hours and cannot get it to work. i have never used vba before this sheet i have tried googling with no luck


So could I please beg and ask that you could possible have a look at the mess I have made and tell me why I am an idiot because what I have done is wrong and help me put it right please?

Thank you

[Palmetto]

The code has to go in the sheet where the "changes" are being made, which is the sheet "Handler Name". You placed the code in the wrong sheet module.

Also - you have merged cells in rows 17:19. Merged cells are almost always problematic for VBA code and, IMO, should not be used.

I've unmerged the cells and copied the numeric values down to row19 and reformatted the cells to retain the appearance you gave using merged cells.

See attached.

[garethtaylor]

Palmetto, you are a gentleman and a scholar, January works fantastic, however when I get to February, March ect on the front sheet cell d12 changes for the first of each of these months and over writes what has already been put. I know I have asked so much already but is there any way February can carry on from January and so on.

I have looked at your code changes and cannot see how I would be able to do this but I am sure this is an easy fix for you.

Thank you, once again.

p.s thank you for letting me know and re formatting the merged cells, I have never used VBA before so any help and tips like this are appreciated

[Palmetto]

January works fantastic, however when I get to February, March ect on the front sheet cell d12 changes for the first of each of these months and over writes what has already been put. I know I have asked so much already but is there any way February can carry on from January and so on.

My fault here - forgot to account for the change in months.

You have a lot of hidden row which I initially failed to detect, but which have to be accounted for in order to offset the correct amount of row so as to get the "day" value. See attached.

Briefly tested the code by changing the selection for the first and last day of each month and all associated cells on the "front" sheet were colored correctly.

Note: due to forum limitations on the amounf of characters permitted in a post some of the Case statements have been omitted in the code below, but not in the attached.

Please Login or Register  to view this content.