Excel 12. Conditional Format Font. Last Day of Month. VBA Case Sensitivity. Email Workbooks

Microsoft will be releasing the new version of Office in late 2006. So far the new Excel version is shaping up to be one of the most exciting updates since Excel 97. Despite rumors that Excel will be moving away from VBA, it looks like VBA will be the language of Excel for a few years yet. Most changes seem to be in area of the removal of some long standing limitations.

3 of the most anticipated changes are as shown below. Personally, I'm not keen to see an increase in total rows available, at least not until Excel becomes a true relational database (multiple tables) rather than a flat line database (single table). The increase in Column numbers is a welcome change, although 16000 seems like over-kill.

The total number of available columns in Excel
Old Limit: 256 (2^8)
New Limit: 16k (2^14)

The total number of available rows in Excel
Old Limit: 64k (2^16)
New Limit: 1M (2^20)

Total amount of PC memory that Excel can use
Old Limit: 1GB
New Limit: Maximum allowed by Windows

To read more about the limitation and get a link to the official Microsoft Blog, go to our forum here .

EXCEL TIPS AND TRICKS

This month I thought I would show you all how we can have font color of specified cells change color based on up to 6 conditions.

Follow this link if you are familiar with Conditional Formatting :
Follow this link if you are not familiar with Excel Custom Formats :

In this example we will have 6 conditions for font color as shown below.

If value less or equal to 0, then make font Red.

If value greater than 0 but less than or equal to 20, then make font Green.

If value greater than 20 but less than 31 , then make font Blue.

If value is between 31 and 40 make font Tan.

If value is between 41 and 50 make font Grey-50%.

If value is greater than, or equal to 51 make font Brown.

To accomplish this we must select the cells to be formatted and then go to Format>Format Cells - Number and use the Custom Format as shown below;

[Red][<=0]0;[Green][<=20]0;[Blue]0

Click Ok to return to Excel. Now with the cells still selected go to Format>Conditional Formatting and set the format criteria as shown below;

1) Condition 1: Cell Value: Between: 31 and 40. Click Format and choose Tan for the Font color.

2) Condition 2: Cell Value: Between: 41 and 50. Click Format and choose Grey-50% for the Font color.

3) Condition 3: Cell Value: Greater than or equal to: 51. Click Format and choose Brown for the Font color.

Now with combination of Custom Formats and Conditional Formatting we have 6 conditions for the font color.

RETURN THE LAST DAY OF ANY GIVEN MONTH

Go here first if you are not familiar with how Excel stores dates and times

One common request is to use Excel to work out the last day of any given month, or date. For example, let's suppose A1 houses the date 23-Jun-2005 and you wish to have Excel reference this cell and return the date of the last day of the month Jun. The formula below will do this;

=DATE(YEAR(A1),MONTH(A1)+1,0)

Where, we are adding 1 month to the date in A1 (June becomes July) and using zero for the day which forces Excel to return the last day of the month before, i.e. July. Or, you can hard code the date as shown below.

=DATE(YEAR("22-Jun-2005"),MONTH("22-Jun-2005")+1,0)

There is a slightly shorter method but it can only be used when/if the Analysis Toolpak is installed. That is, Tools>Add-ins and check Analysis Toolpak. Then you you can use the EOMONTH function as shown below.

=EOMONTH(A1,0)

Or,

=EOMONTH("22-Jun-2005",0)

STOP CASE SENSITIVITY IN VBA

By default, Excel VBA code is case sensitive and uses Binary comparisons. This means that "Cat" and "cat" are not seen as being the same. There are many times however, where you may like Excel VBA to not use Binary comparisons and have "Cat" = "cat". This can be done in at least 2 ways.

UCASE Function

We can use the Ucase function to ensure all text we compare will be in upper case. Take the example macro below which will show a message box if it encounters any cell in A1:A10 of the active sheet that contains any case variation of the word "CAT".

Sub CompareText()
Dim rCell As Range

    For Each rCell In Range("A1:A10")
        If UCase(rCell) = "CAT" Then
            MsgBox rCell.Address & " has " & rCell & " in it"
        End If
    Next rCell
End Sub

Option Compare Text

The other method we can use will make ALL procedures in a specified Module non-case sensitive. We do this by placing the words Option Compare Text at the very top of the Module we wish to make non-case sensitive. For example, any Procedures placed within the same Module as the Procedure below will no longer be case sensitive. To make ALL procedures within the Module case sensitive again we would replace Option Compare Text with Option Compare Binary.

Option Compare Text

----------------------------------------------------------------------------

Sub OptionCompareText()
Dim rCell As Range

    For Each rCell In Range("A1:A10")
        If rCell = "cat" Then
            MsgBox rCell.Address & " has " & rCell & " in it"
        End If
    Next rCell
End Sub

SEND EMAIL FROM EXCEL

Excel VBA allows us to send emails from within Excel and even send the Workbook as an attachment.

SendMail Method

The SendMail Method is very easy to use and will send any specified Excel Workbook as an attachment to specified recipients. As the code below specifies the Active Workbook the code is best stored in your Personal.xls

Sub SendActiveWorkbook()
    ActiveWorkbook.SendMail _
    Recipients:="person@valid-domain.com", _
    Subject:="Try Me " & Format(Date, "dd/mmm/yy")
End Sub

If you only wish to send one Worksheet from a Workbook we can use the method shown below. It creates a new Workbook housing ONLY the sheet we copy. It then sends the 1 sheet Workbook as an attachment, then closes the new Workbook without saving.

Sub Send1Sheet_ActiveWorkbook()
'Create a new Workbook Containing 1 Sheet _
 and sends as attachment.
    With ActiveWorkbook
         .Sheets(1).Copy
         .SendMail Recipients:="dave@ozgrid.com", _
          Subject:="Try Me " & Format(Date, "dd/mmm/yy")
         .Close SaveChanges:=False
    End With
End Sub

Route Method

Another method we can use is the Route Method, it routes the workbook, using the workbook's current routing slip. This allows us to specify numerous recipients and have the Workbook send to the next in the routing slip. When sent, the text below is automatically added to the body of the email;

"The enclosed document has a routing slip. When you are done reviewing this document, choose Send To from the Microsoft Office Excel File menu. Then select Next Routing Recipient to continue the routing."

In the case of the code below, the Workbook would first be sent to man@valid-domain.com who would take the needed action and then go File>Send to>Next Routing Recipient and the Workbook would then be automatically sent to the next Recipient. In this case, person@valid-domain.com

Sub RouteActiveWorkbook()
    With ActiveWorkbook
       .HasRoutingSlip = True
           With .RoutingSlip
                .Delivery = xlOneAfterAnother
                .Recipients = Array("man@valid-domain.com", _
                    "person@valid-domain.com", "lady@valid-domain.com")
                .Subject = "Check This Out"
                .Message = "Please fill in the Workbook and send it on."
          End With
        .Route
    End With
End Sub

Until next month, keep Excelling!

OzGrid Excel and VBA Newsletter

Newsletter Index

EXCEL TIPS AND TRICKS

EXCEL VBA TIPS AND TRICKS