VBA to color All Shapes in a Range

[jhall488]

Hi All,

I am looking to change the color of all shapes with a range when another shape is clicked.

For example i have a shape that is in D9 and when i click it, it changes color but I also have shapes in I9 and J9 that i also need to change color.
I only want to change the color of the 2 shapes in the same row but not any further than +6 columns.

I determine the row and column of the original shape based on:

Please Login or Register  to view this content.

Thank you very much for your help.

[LJMetzger]

Hi jhall488,

See the attached sample file containing the following code. This should get you pretty close to your requirements. If you need additional help, please upload a sample file.


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis