Automformat Excel AutoShapes

Hello All,

I have oval autoshapes in excel that I want to autoformat based on the text value in the shape or based on another cell value:

IF value ="A", shape color should be Amber, "R" to Red, "G' to Green, "C" to Blue, and "NS" to grey

I am very much a VB Excel novice and would appreciate any guidance.

thanks in advance!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The attached workbook demonstrates a solution. Select a color in cell B6 to see the effect.
You can also call the procedure 'AdjustShape' at the top of the 'ColorMan' module to call the same procedure.
To install the program in your project6 drag the module 'ColorMan' to your own project in the VBE Project Explorer window while both workbooks are open. Then copy the procedure from the code module for 'Sheet1(Sheet1)' and paste it to the code module of the sheet in your project where you have your oval. In this procedure you can change the cell where you intend to enter the color code. The procedure will run every time you make a change in that cell. If you enter something there that isn't programmed the displayed color will be gray.

These two lines of code are at the heart of the procedure.
   ColCode = Split("A,R,G,C,NS", ",")
    Colors = Array(4626167, 255, 5287936, 13020235, 10921638)
The first line is a list of your codes. Make sure that they are the same as you have in your drop-down. The second line is a list of corresponding colors. You can change either, but make sure that both have the same number of elements and that the relative positions in both strings are matched. I.e. the first color corresponds with the first color code.

Below the code that does all the work I have added some extra code, all apostrophed out, to help you during development. You can use this code to read or set the name of your oval, its position on the sheet, its color or the color of the border. Note that the Debug.Print command prints to VBE's Immediate Window. The code "Set myOval = ActiveSheet.Shapes(1)" singles out the first shape in your active worksheet for action. If you have more than one shape that won't do. Shapes(2) will return the 2nd shape. From its name property you can find out which shape that is. But then, for easier differentiation, you can assign names to your shapes. For exampe, the shape in my workbook is named "Oval1". You can address that shape by its name, "Set myOval = ActiveSheet.Shapes("Overal1")" instead of by its index number.

I think you are all set to go. Best of luck!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gcSalloumAuthor Commented:
Thank You Faustulus. This was perfect!

All the Best !
I'm really glad I hit this nail on the head. I was a bit worried because the subject is intricate. If you have any more questions while implementing you can come back here even after you closed the question officially.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.