Excel: Nice Shades

If you are using conditional formatting to highlight cells, you can run into problems when you delete/add rows. Sometimes the conditional formatting rules apply to a strange set of cells instead of one contiguous range of cells.

To get around this issue, you can use a macro to do the highlighting. Open the VBA editor (with Alt-F10) and then add the following code to Microsoft Excel Objects --> ThisWorkbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'shade cell with "yes"

If Target.Row > 1 And Target.Column = 2 Then
    If Target.Value = "yes" Then
        Cells(Target.Row, Target.Column).Interior.ColorIndex = 50
    Else
        Cells(Target.Row, Target.Column).Interior.ColorIndex = 2
    End If
End If

This code is run every time you edit a cell. The code makes sure the current cell is not the 1st row (which is usually for headings). It then checks if the cell is in column B (which is column number 2). If the contents of the cell are "yes" it gets green shading, otherwise is gets white shading.

The only downside to this method is that it runs only after editing a cell. Therefore, if you already have  cells with information, you have to re-edit each cell to get the macro to run. This can be done somewhat easily by hitting F2, then the Enter key. For large amounts of data, you need to write a wrapper macro that you can run once on the sheet.

Excel: Removing Blank Rows

Excel has a powerful filtering capability that lets you view, among other options, the data without blank rows. But how do you remove the blank rows from your data? The following VBA code does the following:

1. Select a cell in the maximum row
2. Check if the selection is blank
3. Delete if blank
4. Move up 1 cell

      Dim x As Integer
      ' Set numrows = number of rows of data.
      NumRows = 1400 -2
      ' Select max cell
      Range("B1400").Select
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To NumRows
         ' Insert your code here.
         If Selection.Text = "" Then
            Selection.EntireRow.Delete
         End If
         ' Selects cell down 1 row from active cell.
         ActiveCell.Offset(-1, 0).Select

There is probably a smarter way to select the maximum number of rows, but for my data it was fixed.

Excel Macro for Background Color

I was writing an Excel Macro and I needed to set the background color of a cell based on the text value. To do this I wanted to use Conditional Formatting, and I found out that you cannot (AFAIK) setup Conditional Formatting from inside of a macro. But, you can create a macro that loops through a range and sets the formatting.

Here is how:

1. Open the VB editor (Alt + F11)
2. Open ThisWorkbook under Microsoft Excel Objects
3. Add the following code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Set myRange = Range("C4:C104")

    For Each Cell In myRange

        If Cell.Value = "n" Then

            Cell.Interior.ColorIndex = 3

        End If

    Next

End Sub

Anytime  a change is made to the workbook, the macro is called. It checks a range of cells, and then if the text is 'n' it sets the background color to 3. But what is '3'?

Changing Weekend in Excel Workday

If you live somewhere where the weekend is on Friday and Saturday instead of Saturday and Sunday, here is a hack to the Excel Workday function to calculate the correct dates:

WORKDAY(K5+1,5)-1

where K5 holds the date. Just add 1 to the date and subtract 1 from the result.

[source here]