How do I modify the colour of a text box that is linked to the value of a cell

May I see an example of what you have and what you want it to look like?

You need to identify the correct textbox - but then using the change event of the data sheet, you can use

Please Login or Register  to view this content.

Upload something as an example of what it is that you want with the desired solution and that will most likely help us understand better. Thanks.

As far as a macro to recognise color here is an example below. As far as writing you a macro for you without you having to do the work please upload something.

Please Login or Register  to view this content.

Here are the color indexs
http://www.excel-pratique.com/en/vba/colors.php

One way would be to create your map with "cutouts" that allow parts of cells to be seen at the points of interest, and use the same CF on the cells that show through, linking them as required, and formatting them as ;;; to hide any linked value. If a cutout covers more than one cell, all the visible cells can all be linked to the same value so that the formatting coverage is complete.

You should be able to modify Bernies' approach.

Please Login or Register  to view this content.

If you change the value of Data!B2 and the CF associated with it the text box on Map will update.

I like that - and I especially like the DisplayFormat property. I've learned something today!

Hi Andy,

Thanks a lot for the solution, I see it works in your file, but whenever I'm trying to implement it to mine it doesn't work.
Can you be more specific to where I implement this code? I tried to assign the macro directly to the cell but it doesn't work.

As I could see the text box is already filled in with the color (I don't know whether by the macro or manual). I've changed the CF of Data!B2 to another color and it hasn't replicate the color to the text box.

Please advise,

Radu

Once you change the CF you need to update the cell by changing or reentering the value. This will cause the change event to fire for the sheet and the code will update the textbox.

Conditional formatting change will not fire an event. So if the CF for B2 is based on formula from other cell you will need to capture a different event or specify which cells to capture.

If you are still stuck post example file so we can see what is actually happening

Right click sheet tab of the data worksheet and post code in that sheet object

Hi Andy,

I managed to put the source to the page, it works, but only when I modify cell by cell (it doesn't work when I put information in multiple cells).
And also I have multiple columns of information on the data sheet. How do I modify the macro to modify the color of text boxes for multiple sheets .
I've tried to copy the code below the first one, changed the worksheet name to the other ones, but I receive an error.

Thanks in advance,

Radu

You only need the code in the object of the sheet with the values in.
If you have shapes on multiple sheets then you will need to alter the sheet object that is used.
If target is a range of more than 1 cell you will need to loop through the cells.

Please Login or Register  to view this content.