Go Ad-Free on CAclubindia with Daily Mobile Updates. Subscribe Now!

Learn Excel Macros

Vinod (Assistant Audit Officer) (1675 Points)

07 July 2008

Dear club members,

Here I call upon this learned community to share your knowledge about Excel Macros in such a way that the beginners in this field are able to learn easily.

So plz contribute here and spread the power of knowledge.

Vinod

Reply
Follow
Share

3 Replies

pravanjan mishra (Accountant) (53 Points)
Replied 07 July 2008

Dear what am i actually think that inorder to convert an excel file to Hex file you must keep down the macros low from Tools menu-Macros-security-low.Hex file actually very small in size & contains text data only.But we should all have to remembered that Antivius software must be loaded in the system.

Upgrade to CAclubindia Pro. Subscribe Now!

Gopal (17 Points)
Replied 08 July 2008

One of the most difficult operations in Excel is that one cannot sum a set of cells which have a similar color.

This is useful where the financials are prepared linked to an excel trial balance. To illustrate if all the assets were to be colored green in tb and the assets in balance sheet have a similar color, one can check whether the links to tb are correct. This facility of summing by colors is not available in excel.

To use this feature, one has to copy the 6 modules in visual basic editor. This is found in Tools, Macro, Visual basic editor. when you click on this 6 modules would be present- this you need to copy in the file where you would use this.

Then cellcolorindex would give the color of the cell, which is used by sumbycolor, countbycolor formula. This would give the sum/ count of the cells which have a particular color.

Please enable micros before using this feature.

If you have any queries, contact me at engopal @ gmail.com

Happy computing

gopal

Attached File : 43 color workbook.xls downloaded: 424 times