OzGrid Excel and VBA Newsletter

Software Categories Search Software

Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software

COMPLETE EXCEL COURSE HALF PRICE SPECIAL!

Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

EXCEL TIPS AND TRICKS

Conditional Formatting

In Excel 97, Microsoft introduced a nifty new feature called Conditional Formatting. It can be found via the Worksheet Menu Bar under "Format". It allows us to format a cell, or range of cells, based on a specified criteria for the cell(s), or other cell(s).

A simple example would be to use Conditional Formatting to highlight all cells in any given range that are greater than 100. Let's say this range is A1:A100. We would Start by selecting cells A1:A100, Starting from cell A1. This will ensure A1 is the active cell in the selection and from this, Excel will know all other cells we specify are relative to cell housing the Conditional Formatting. Now go to Format>Conditional Formatting and then choose "Cell value is", then "Greater than" and then type 100 in the far right, see below;

Now click the "Format" button and choose the desired formatting for all cells greater than 100. After this, click "Ok" then "Ok" again. The same logic can be applied to any other of the criteria we can choose. E.g "Less than", "Equal to" etc

The only drawback with this way, is that cells housing text may be seen as having values greater than 100! We can over-come this by resorting to the "Formula is" option as apposed to "Cell value is". When using the "Formula is" option, any formula we use, MUST evaluate to either True, or False. With this in mind, here is a formula that we can use in place of simply 100.

=AND(ISNUMBER(A1),A1>100)

Again, it is vital the you select Starting from A1 as all other formulas, in A2:A100, will change their cell references relatively. That is, A2 Conditional Formatting will read;

=AND(ISNUMBER(A2),A2>100)

If you are not already aware, both conditions in an AND Function must evaluate to True for the Function to return True. In other words, all cells in A1:A100 will need to house a number AND the number must be greater than 100 for the chosen formatting to be applied.

Download Some Great Example Uses for Conditional Formatting!

Conditional Formatting for Duplicates

Now you are familiar with Excel's nifty Conditional Formatting feature, we move into a great use for it! That is, highlight duplicates on an Excel Spreadsheet. It is far easier than you may think.

Let us assume the range we want to highlight duplicates is the range A1:A100.

1) Select range A1:A100 Starting from A1. This will ensure the active cell is A1

2) Go to Format>Conditional Formatting and choose "Formula is"

3) In the right hand box type the formula as show below;

=COUNTIF($A$1:$A$100,A1)>1

4) Click the "Format" button and choose any desired format to apply to any duplicate entries.

5) Click "Ok" then "Ok" again and we are done!

Now, all duplicates in the range A1:A100 will have the format you have chosen. It is important to note the Absolution of $A$1:$A$100 in the COUNTIF function and that the single reference (A1) is a Relative reference. This is why step 1 states to Start from A1 (in this case) ensuring A1 becomes the active cell. The "Formula is" in the Conditional Formatting for A2 will change automatically to;

=COUNTIF($A$1:$A$100,A2)>1

Note the $A$1:$A$100 never changes due to the use of Absolution on both the column ("A") and the rows (1:100)

Download Some More Great Example Uses for Conditional Formatting!

EXCEL VBA TIPS AND TRICKS

Excel Intersect Method

The Intersect Method will return a Range Object that represents the intersection of two, or more, ranges. It can be used to determine if a specified Range Object intersects another specified range(s). Or, in layman's terms, does a specified range intersect another range(s).

To explain this, simply copy and paste the macro below into any standard Module. Then, ensure the active cell is within A1:A10, and run the code. It will return the address of the active cell.

Sub TryMe()
	MsgBox Intersect(ActiveCell, Range("A1:A10")).Address
End Sub

If the active cell is not within A1:A10 a Run Time Error occurs. This is because the Intersect Method is unable to return a Range Object, and hence any address of the Intersect. It is important to be fully aware of this with the Intersect Method as it can catch out the unaware.

Ok, so how do we overcome the possibility of a Run Time error when/if our specified range to check does not Intersect another specified range, or ranges? To know the answer to this we must first understand what IS returned to Excel when/if a Range Object fails. What is returned is the keyword Nothing. The keyword Nothing tells Excel that what should be a Range Object is not. The Nothing keyword is mostly used to clear an Object variable back to its default of Nothing. For example, the code below would clear the Object Variable MyObject.

Set MyObject = Nothing

This would only be needed when/if the Object Variable MyObject has been Set to any Object, e.g. a Range Object.

Ok, so we now we know the meaning of Nothing, we can use code like shown below to prevent the Run Time error we get when/if the active cell does not Intersect A1:A10

Sub TryMeAgain()
	If Intersect(ActiveCell, Range("A1:A10")) Is Nothing Then
		MsgBox "The active cell does NOT Intersect A1:A10"
	Else
		MsgBox "The active cell does Intersect A1:A10"

	End If
End Sub

In this case, instead of a Run Time error when the active cell does not Intersect A1:A10, we get a message box telling us so.

Some Practical Uses for the Intersect Method

One of the most popular uses for the Intersect Method is to use it to determine whether a changed cell is part of another range, or ranges. If you are not already aware, Excel has a built in Worksheet_Change Event that is fired when any change of data occurs on the Worksheet that houses the Worksheet_Change code. Excel's Event code fits under two main headings

1) Workbook Events

2) Sheet Events

Workbook Events

The quickest way to get to Excels Workbook Events is to right click on the sheet picture (top left next to "File") and select "View Code". This will take you directly to the Private Module of the Workbook Object ("ThisWorkbook"). Then you will need to select "Workbook" from the "Object" drop down box in the top left (where it reads "General"). This will default to the Workbook_Open Event. You can choose other Events from the "Procedure" drop down box to the right of the "Object" drop down box.

Sheet Events

The quickest way to get to Excels Sheet Events is to right click on the sheet name tab and select "View Code". This will take you directly to the
Private Module of the Sheet Object, e.g. "Sheet1". Then you will need to select "Worksheet" from the "Object" drop down box at the top left (where it reads "General"). This will default to the Worksheet_SelectionChange Event. You can choose other Events from the "Procedure" drop down box to the right of the "Object" drop down box.

Go here to learn more about Excel Events

Back to the Intersect Method. Place the code below into the Private Module of the active Worksheet, then close the VBE so that you are back to the Worksheet and change any cell by entering any data.

Private Sub Worksheet_Change(ByVal Target As Range)
	MsgBox Target.Address
End Sub

You will soon see that the keyword Target always returns the Range Object of the cell that fired the Worksheet_Change Event.So, armed with this knowledge we can now use the Intersect Method to take some action when/if specified cell is changed. For example;

Private Sub Worksheet_Change(ByVal Target As Range)
	If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
		MsgBox "Hello"
	End If

End Sub

will only display the "Hello" message if the cell that fired the Worksheet_Change Event Intersects A1:A10 of the Sheet housing the code. Note the use of the Not Operator to reverse the logic of the If Statement, i.e TRUE when FALSE and FALSE when TRUE.

If you need to check against a non contiguous range(s) (multiple ranges not sharing common boundaries) we can use code like show below;

Private Sub Worksheet_Change(ByVal Target As Range)

		If Not Intersect(Target, Range("A1,B2,C5")) Is Nothing Then

			MsgBox "Hello"

		End If

End Sub

If you need to check against a non contiguous range(s) and contiguous ranges we can use code like show below;

Private Sub Worksheet_Change(ByVal Target As Range)
	If Not Intersect(Target, Range("A1:A10,B2,C5:D10")) Is Nothing Then
		MsgBox "Hello"
	End If
End Sub

When/if we are checking against a named range (good practice, especially with VBA) we should first check to ensure the named ranges exists. This is needed to prevent a Run Time Error if the named range does not exist and always return Nothing from the Intersect Method. Below is how we could use the Intersect Method to check if a range (Target in this case) intersects a named range.

Private Sub Worksheet_Change(ByVal Target As Range)
	On Error Resume Next
		If Range("MyRange") Is Nothing Then Exit Sub
	On Error GoTo 0

	If Not Intersect(Target, Range("MyRange")) Is Nothing Then
		MsgBox "Hello"
	End If
End Sub

Note the use of On Error Resume Next. This is needed to stop any/all Run Time errors if the named ranges "MyRange" does not exist. While the Exit Sub tells Excel to do no more and exit the Sub Procedure. On Error GoTo 0 resets all error trapping and allows Excel to again bug out on Run Time errors. However, this does happen automatically when the Procedure ends, or encounters Exit Sub.

Until next month, keep Excelling!ADVERTISEMENTSArtificial neural network software for stock markets! EXCEL TEMPLATES SPECIALS DATABASE SOFTWARE