Using VBA to change the colour of a shape to match cell text

Thanks in advance for taking the time to read my post and for any assistance offered.

I have attached an example of my worksheet. The example shows 3 categories with Red, Amber and Green dropdowns which in turn have appropriate conditional formatting to colour the cell based on the value selected.

I would now like to apply the same approach but to shapes.

My real wold worksheet has 31 categories in it.

I have had many attempts at putting some code together and ultimately I am failing. I have looked at using a for next loop as I thought this would also reduce the amount of 'wasted' code due to the repetitive nature of the code.

For ease of coding, I have called the shapes Shape1, Shape2 and Shape3. This will increase to 31 in my real world document

I am using Excel for MAC v16.20(181208)

Any help gratefully received

This was written with the assumption the the correlation of row number to shape name will be valid.

1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes to the range of CF cells as needed

Please Login or Register  to view this content.

Brilliant, thank you for the quick response and solution. I don't have time to fully check it now, but at first glance, it appears to do the job

I appreciate the help

Hi there. Firstly, thank you once again for taking the time to assist me.

I have managed to implement your solution into my main workbook and I have noticed a couple of things.

In my document the RAG status is defined by calculation, with the author having the ability to amend the RAG colour, using the dropdown.

When I select the worksheet with the shapes and dropdowns on the shapes have not changed colour, they only change once the author physically changes the cell value.

Is there a way using something like 3 lines of code (Red, Amber, Green) such as

Please Login or Register  to view this content.

and a for next loop to cycle through the 31 shapes?

I should have stated I do not need to match the exact cell colour in the shape, it is a simple Red, Amber, Green Status, however I do appreciate the intention

Also I note I am unable to use my command key or 'Undo' in the edit menu on the worksheet in question, is that normal?

Thank you

We can use the calculate event, and loop through all the cells with the formulas/CF.

The clearing of the Undo stack is an unfortunate side effect of running VBA code

Please Login or Register  to view this content.

The clearing of the Undo stack is an unfortunate side effect of running VBA code

Forgive my naivety but I don't understand that, my other sheets, with VBA code, allow me to undo?

That aside, what does the code look like to achieve the loop through? I didn't think you could apply a conditional format to a shape?

It depends on the specific code - some changes clear the stack and others don't.

The code that I posted loops through the cells and reads the CF that they are showing, then applies that color to the corresponding shape.

Thank you.

I have amended the code for my real world workbook. I am getting an error now.

Scores are entered on Sheet 1
Calculated results and RAG status is on Sheet 2, the same sheet as the 'tbox' Shapes are on.
This is my amended code, tbox instead of shape and R8:R38 is the range with an adjustment of -7 for the row calculation.

Please Login or Register  to view this content.

Should the activesheet be change to the actual sheet reference seeing as the scores are done in a separate worksheet?

Thanks again

I have changed

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

That appears to have done the trick

Sorry - I did not realize it was a multi-sheet workbook - it's great that you got the code to work!

Let me attempt to be more clear with my real world workbook, which I have many sheets in, Apologies for not making this clear.

I have a question worksheet which a user selects and a score is assigned. (AUDIT)
I have a results worksheet, where the calculations are formula driven based on the scores in AUDIT. (RESULTS)
I have a report worksheet where the RAG status is calculated based on the scores from the previous worksheet. (report-Triangle)
On the same report worksheet, I also have an additional column where the assessor has the ability to override the RAG status, using a Red, Amber, Green dropdown.

Therefore the report-triangle worksheet shows the calculated RAG status and a final RAG Status (the assessor status override)

The shape colours are based on the override cell values, in my example R8:R38


So, now I have a different issue.

When I go into the shape worksheet the colours of the shapes are coloured correctly, as per the code provided, thank you.
If I want to override the RAG status, on the shape worksheet, using the RAG dropdown (as described above) then the shapes do not change colour.

Am I able to have both?
Ideally, when the user clicks into the shape worksheet the colours are what the RAG calculation should be, however, if the assessor overrides the RAG status in the overrride column (R8:R38) then can the shape colour automatically change to the new RAG status?

Of course - you just need to have some logic based on your process and your workbook structure, along the lines of this (pseudo code that needs the correct RGB values, etc:


Replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

May be a dumb question, but what is "D" relating to ?

Column D - the range object Cells take a row number and either a column number or column letter, like

Cells(3,"D")
or
Cells(3,4)

both refer to cell D3.

But note: Cells("D3") would result in an error; Range("D3") is required if you have a string reference for the address.

Thank you once again but I am not making any progress here, that has not changed anything.

This is my code so far, with the amendments included and the names/references reflect a copy of my real-world document.

The range is R8:R38. I have tried both Cells and Range and bot "R" and 18

Please Login or Register  to view this content.

Which sheet is the code in? Which sheet has the cells R8:R39? Which cells can have the Red Amber Green values manually entered (rather than from a formula)? Can you share another version of your workbook with an explanation of when values are entered from a dropdown?

Bernie

Thank you for your continued patience.

OK, so I attach a copy of my real-world example with all the code stripped out.

Assume the RESULTS tab is where a whole load of pre-calculation ends up with a RAG status.
On the REPORT-TRIANGLE tab, you will see that column C displays the calculated RAG from the Results sheet, using a simple =cellvalue formula.

In column R you will then the same RAG status, however, this is where the author can adjust from one colour to another.

For Example - C8 may display Red but the author can change C8 to Amber.


What I am firstly seeking to occur is for the shape colours to update when the Report-Triangle worksheet is opened, to reflect the colours in Column R
Then ideally, once in the worksheet, if the author changes a RAG status then the corresponding shape colour will change whilst in the worksheet to the new selection in Column R

Hope that makes more sense?!

I really am very grateful for your persistence in helping me here.

This version just looks at the values in column R. Note that if you overwrite a formula in R, Excel may not have re-calculated, so I created links to the cells in R elswhere on the sheet (In column T, formatted as ;;; to hide the values) which force a recalc when a formula in R is overwsritten.

Note that the code is in the codemodule of the sheet "Report-Triangle" so I used Me in place of Worksheets("Report-Triangle")

Thank you, what you describe makes perfect sense. Sterling work and I am really very grateful, thank you

Happy to help you out - you're very welcome.