Andreas Hermle
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
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.
Help is much appreciated.
Thank you very much in advance for your professional help.
Regards, Andreas
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.
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
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
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. :/