• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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!
  • 2
1 Solution
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!
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now