Upper Level Visualization – Auto Colored Shapes

Any data (table, text, etc…) can be more meaningful for anyone with some visualization. Graphs and charts can be colored easily by excel itself. But sometimes it is much simpler to describe something with perfectly coordinately shapes. In excel, coloring shapes through data exist in cells could be complicated. In this post you can find the macro for auto coloring the shapes.

I spend some efforts to make that macro more reusable but still there are some constrains. So lets move on with how to use that coloring macro;

P.S. Before go on with the post, check how to import macros post, to learn how to import the provided macro module.

Start with importing the provided module (Save Target As).

You can download simple example from here. I am going to describe the usage of the macro through this document.

Main aim in this example is to visualize the data in the Sheet “DataSheet”, by coloring the shapes in the “ColoredShapes” sheet according to that data.

In “DataSheet” sheet there are names and values for each name. In “ColoredShapes” sheet, from column B to E, initialization required for usage of the macro, plus there exist three shapes which have names in the data sheet as names.

Step by step description for usage;

1- Create the Data

  • This data can be any two column data. Position (can be in any sheet and can start from any cell) and the type of the value is also not important.

2- Make a map or shape combination

  • There must be at least 1 shape for each row exist in the data table.
  • Name of the shapes must be same with the values in the first column of the data table.

3- Initialize the macro

  • This initializing tables position must be same with in the example (example: “Sheet” must be in B2)
  • C2 which is name of the sheet that includes the data table. (example: “DataSheet”, if you have your data table in another sheet set this property to name of this sheet)
  • C3 is the column number for name property.(example: name_1 and other names are listed in column# 2, so C3 set to 2. If your names listed in another column, set this property to that column number)
  • C4 is the column number for value property.(example: “1″ and other values are listed in column# 3, so C3 set to 3. If your values listed in another column, set this property to that column number)
  • C5 is the row number for first data(example: value is “3″ because first data in datasheet is at that row. If your first data in another row, set this property to that row number)
  • C6 is the row number for last data(example: value is “5″ because last data in datasheet is at that row. If your last data in another row, set this property to that row number)
  • From the look up table, you can assign RGB values for any value. There must be row for each value given in data table. It can be done by “x,y,z,w” where x=value, y=Red, z=Green, w=Blue

lookup

figure: 1

4- Run Macro

  • If you are not already created a button and assign macro to it at the beginning you can check how to do it from how to import macros post.

After completed all steps described above it will be smart to hide the columns B to E.

You can find two examples here, that show the ways that you can use this macro;

Posted in Macros

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>