CODEWITHSUNDEEP
excelvba
Rehan
Rehan

Reputation: 1

How to move shapes along with the cells when sorting by rows in excel

Background: I am using shapes to assign multiple colors to a cell.

Example: To make a cell half yellow and half green. I make the cell green and add a rectangle yellow shape on the top, covering half of the cell.

Problem: Problem is, when I use "Sort" feature on excel to sort entire rows by dates( i have a column with dates) it obviously moves the colored cells accordingly BUT!! The colored shapes do not move at all.

How can I make colored shapes to move with the cells? is there a way to permanently link the shape with a cell or a row so it moves with the rows?

Any kind of help will be greatly appreciated.

Btw, I am using the following code to generate these shapes according to the selected cell.

Dim shp As Shape

Set shp = ActiveSheet.shapes.AddShape(msoShapeRectangle, Selection.Left, Selection.Top, 25, Selection.RowHeight)

Thanks,

Upvotes: 0

Views: 781

Answers (1)

Frank
Frank

Reputation: 61

It seems that the key is to have your shape very slightly inside the cell. That is, rather than putting it right at the top-left corner with the same height as the cell, place it a point below and a point to the right of the top-left corner with a height 2 points shorter than the cell:

Dim shp As Shape

Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, Selection.Left + 1, Selection.Top + 1, 24, Selection.RowHeight - 2)

That works for me in Excel (Office 365), I hope it helps you!

Upvotes: 2

Related Questions