Link to home
Start Free TrialLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on

Fill cells with a specific shading with a hyperlink

Dear Experts:

I wonder if somebody could help me with the following:

My current worksheet has a 5000 data records.

I would like a VBA macro to perform the following actions on this data list:

... Go to the column header that says: 'Hyperlinks_Paths' (Column Headers are located in the first row)
... go down one cell so that the first cell right under the Column Header  'Hyperlinks_Paths' is the beginning of the range
... Set a range that spans down to the last filled cell in that column
... Insert a hyperlink (C:\MyFolder\DummyGraphic.png) in all the cells which have the following cell shading: RGB (222, 222, 222). Existing cell contents in these cells is to be overwritten with this Hyperlink text.

Help is much appreciated.

Thank you very much in advance for your professional help.

Regards, Andreas
ASKER CERTIFIED SOLUTION
Avatar of Steven Harris
Steven Harris
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Zack Barresse
Zack Barresse
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
While those are valid points Zack, they are not necessarily shortcomings.  

1) I do not explicitly call a sheet so that the macro can be used on multiple sheets within a workbook.  the OP stated that "My current worksheet has a 5000 data records." so I took that to mean that this could grow exponentially, possibly into other sheets.

2) You are correct about the hard coding; however, it leaves more options for an inexperienced VBA user to manipulate if needed.

As for the ScreenUpdating, I honestly do not even remember setting that for this macro, as it is not really needed. :/
Humbly but definitively disagree. :) I see them as shortcomings to robust and efficient coding, especially if attempting to use best practices. If specifically asked for, I can see working on the active sheet, but nothing else besides that. It certainly should NOT be viewed to "leave more options for an inexperienced VBA user" regardless of what they want to manipulate. It's up to people like you and I to point these pitfalls and shortcomings to users. ;)

Regards,
Zack
We shall agree to disagree then.  While you are right, and this would not be a code that I myself would use (for many reasons), this is a very basic and easy code using fundamental properties that can easily be manipulated by a novice who is willing and wanting to learn.  Once they can grasp some of the basics, they can move on to a level that you are proposing.
Avatar of Andreas Hermle

ASKER

Dear both,

thank you very much for your swift and professiona support. I can understand both positions and therefore share the points.

thank you very much for your great help.

Regards, Andreas