Conditional Chart Axis Highlighting. Delete VBA Code With Code

Merge/Consolidate Excel. New & Great Value! Excel-Cube merges, or consolidates, entire worksheets across standard Excel workbook files without using linking formulas, add-ins, or macros.

Tradecision Software has applied its thorough knowledge and expertise in developing efficient trading software and artificial neural networks. Experts created this powerful tool drawing on the experience of professional and successful traders all around the globe.See many video tours here

Finance Templates Gold Edition: All at 80% off AND $139.00 of free software! PLUS a bonus pack of 30 extra FREE financial templates

EXCEL TIPS AND TRICKS

Conditional Highlighting Axis Labels

This month we thought we would show you a nifty trick to highlight x axis labels when data drops below a certain range. Notice that the base for this article is Excel 2007, although instructions to make this work in prior versions are included. This article was supplied byAndy Pope and also features in our second edition of Excel Hacks – 100 Industrial Strength Tips & Tools, to be released around mid 2007.

When you create a chart, you can highlight X-axis category labels in a certain color when the data drops below a certain range. You can do this by creating a clustered column, then adding the data labels of two extra data series and plotting these extra series as lines.

Let’s say you had created a chart and you wanted to show horizontal (x) axis value labels in red when your data drops below 25 and in blue when the data is above 25. Here is how you can do this.

Set up a spreadsheet like in the attached workbook which shows the data and formulas used to build the chart. The actual data for the column chart is in the range C3:C14. The formula in columns D and E test the data value and either output a zero or #N/A depending on the value typed in column C and therefore determining whether a red or blue label should be displayed.

Using this data will highlight horizontal (X) axis labels in red when the monthly data drops below 25.

First we need to create the basic chart, so highlight the data in C3:C14 and go to the Insert tab. Under Chart options, select Columns and choose the first column chart (pre 2007, use the Chart Wizard). Go to the Layout tab and choose Series Red Label from the drop-down menu under the current selection. Now, go to the Design tab and choose Type>Change Chart Type. Select a Line chart from the menu presented. Then, do the same thing for the Blue Label series. (In versions of Excel prior to 2007, you will need to highlight the series, right-click, and select Chart Type).

Once again, go to the Layout tab, and select Series Red Label from the drop-down menu. Choose Labels>Data Labels>More Data Label Options (pre 2007, double-click on the series and select Data Labels), and check Category under Label Options. Then, do the same for the Blue Label series. Make sure that Value is not selected under Category name.

Yet again, go to the Layout tab, and select Series Red Label from the drop-down menu. Select Data Labels>Below (pre 2007, right-click on the data series, select the Alignment tab, and change the Label Position: to Below), and then do the same for the Blue Label series.

Highlight your original horizontal (X) axis, right-click your mouse, and select Format Axis (pre 2007, double-click the horizontal axis and select the Patterns tab). Under Axis Options, set the Major tick mark style to None and the Axis Labels to None. This will clear the original axis.

Finally, highlight the Red Labels data labels and go to the Home tab (pre 2007, use the Formatting toolbar). Under Font, change the Font color to Red and then do the same with the Blue Labels, formatting the font color to blue. Your final chart, with conditional highlighting, will show the x axis labels for the months May, September, October, December in red, as the values for these months fall below 25. All other axis labels will be blue, as the values for these months rise above 25.

EXCEL VBA TIPS AND TRICKS

This month I thought we would look at coding to the VBE (Visual Basic Editor), in particular the Sheet and Workbook Objects. Perhaps the most common need for this is when you wish to write code using code. However, in this case, it is often better to create a template Sheet and/or Workbook Template and simply add/insert when/as needed.

The 2nd most popular reason for coding to the VBE could be when we need to delete/remove existing code in either the Sheet Object, or Workbook Object.

Delete Sheet Event Code With Code

The macro below can be used to delete Sheet Code based on the Sheets CodeName supplied to theSelect Case Statement within the Sheets Collection Loop. It's ideal to use when you need to copy Sheets from a Workbook but do not want the code to copy with the Sheets.

You will have to go to Tools>Macro>Security - Trusted Publishers and check Trust access to Visual Basic Editor before running the code. You must also set a reference to the Microsoft Visual Basic For Applications Extensibility. This is done while in the VBE and going to Tools>References.

In the case of running this code from a different Workbook than the Sheet event code resides in, you must specify the Workbook by name and BOTH Workbooks must be open. That is, replace

For Each sSheet In Sheets

With code like;

For Each sSheet In Workbooks("Book1.xls").Sheets

Workbook Event Code

Excel is rich inEvent Code for both the Workbook Object and the Sheet Object. For example, theWorkbook Open Event is possibly the most popular of
the Workbook Events (ThisWorkbook).

You will have to go to Tools>Macro>Security - Trusted Publishers and check Trust access to Visual Basic Editor before running the code

Again, in the case of running this code from a different Workbook than the one the Workbook event code resides in, you must specify the Workbook by name and BOTH Workbooks must be open. That is, replace

ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule

With code like;

Workbooks("Book1.xls").VBProject.VBComponents("ThisWorkbook").CodeModule

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

OzGrid Excel and VBA Newsletter March 2007

Excel Newsletter Index

EXCEL TIPS AND TRICKS

Conditional Highlighting Axis Labels

EXCEL VBA TIPS AND TRICKS

Delete Sheet Event Code With Code

Workbook Event Code