Macro to Shift object

[maggotnap]

Hi,

I have cells I want to highlighted with a circle on a budget. I want to have a shape named "Oval 3", do the highlighting. This is clearly easy to do manually.

However, I am trying to shift the circle (Oval 3) from Cell A10, to B10 (19pixels) and onward to Z10(based on a cell "A1". A1 is populated with today's ("=today()") date. So as the date moves/increases on I want the circle to move to the new (next) cell over on row 10, to highlight the remaining cash/budget.

Column length is 19 pixels

Anyone have any ideas?

 

[Smitty]

Welcome to the Board!

Take a look at the TopLeftCell property in the VBA helpfile. You can position the object based on that.

HTH,

 

[maggotnap]

Thanks for your reply,
But sorry not sure what how to proceed.

 

[Smitty]

What you want to do can only be accomplished with VBA code.

You can record a macro moving the oval to the positions you want, which will give you an idea about how VBA works with object placement.

Then I'd probably use Application.WorksheetFunction.Match to locate the current date in the data, and once you've identified it, use the TopLeftCell property based on that location to place the oval.

Unfortunately, I don't really have time to create an example, but if you get stumped I'll see what I can come up with a bit later.