Tag: excel

Adding Newlines in Excel

There are several issues with newlines in Excel. First, how do you add one as you are typing in a cell. And second, how can you use Find and Replace to add newlines?

When Typing

Simply use “Alt+Enter” to add a newline in the current cell.

      Using Find and Replace

      To add a newline with Find and Replace, let’s assume we have the following cell:

      First, open the Find and Replace window (using Ctrl+h).

        In our case, we have a semicolon between each letter. So, enter “; ” as the Find what.

        In the Replace with field, press Alt. Then type 010. Then let go of the Alt key. You won’t see anything different in the field, but trust me, it is there.

        Click Find Next and then Replace or just click Replace All.

        Note that to see your results better, you might need to select your cells, and then click Format > Autofit Row Height.

        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’?
        Excel uses an object called ColorIndex which takes an integer as its argument. The integer is one of 56 colors, given by the following table (a link to the source is below):

        [Color 0] [Color 0] [Color 15] [Color 15] [Color 30] [Color 30] [Color 45] [Color 45]
        [Color 1] [Color 1] [Color 16] [Color 16] [Color 31] [Color 31] [Color 46] [Color 46]
        [Color 2] [Color 2] [Color 17] [Color 17] [Color 32] [Color 32] [Color 47] [Color 47]
        [Color 3] [Color 3] [Color 18] [Color 18] [Color 33] [Color 33] [Color 48] [Color 48]
        [Color 4] [Color 4] [Color 19] [Color 19] [Color 34] [Color 34] [Color 49] [Color 49]
        [Color 5] [Color 5] [Color 20] [Color 20] [Color 35] [Color 35] [Color 50] [Color 50]
        [Color 6] [Color 6] [Color 21] [Color 21] [Color 36] [Color 36] [Color 51] [Color 51]
        [Color 7] [Color 7] [Color 22] [Color 22] [Color 37] [Color 37] [Color 52] [Color 52]
        [Color 8] [Color 8] [Color 23] [Color 23] [Color 38] [Color 38] [Color 53] [Color 53]
        [Color 9] [Color 9] [Color 24] [Color 24] [Color 39] [Color 39] [Color 54] [Color 54]
        [Color 10] [Color 10] [Color 25] [Color 25] [Color 40] [Color 40] [Color 55] [Color 55]
        [Color 11] [Color 11] [Color 26] [Color 26] [Color 41] [Color 41] [Color 56] [Color 56]
        [Color 12] [Color 12] [Color 27] [Color 27] [Color 42] [Color 42]
        [Color 13] [Color 13] [Color 28] [Color 28] [Color 43] [Color 43]
        [Color 14] [Color 14] [Color 29] [Color 29] [Color 44] [Color 44]
        A more complex example is shown below. This is used in a ToDo sheet. It checks the “Done” column (C) to see if  the cell is marked ‘y’ or ‘n’ and sets the corresponding color. If the task is done (C=’y’) then it also sets the cell adjacent tothe color gray with strikethrough text.
        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
                If Cell.Value = “y” Then
                    Cell.Interior.ColorIndex = 10
                End If
                If Cell.Value “n” And Cell.Value “y” Then
                Cell.Interior.ColorIndex = xlNone
                End If
               
                ‘ add strikethrough
                Dim myRow As Integer
                Dim myCol As Integer
                myRow = Cell.Row
                myCol = Cell.Column – 1
               
                If Cell.Value = “y” Then
                    Cells(myRow, myCol).Font.Strikethrough = True
                    Cells(myRow, myCol).Interior.ColorIndex = 15
                End If
               
                If Cell.Value = “n” Then
                    Cells(myRow, myCol).Font.Strikethrough = False
                End If
            Next
        End Sub
        Happy coding 🙂

        [Source MVPS site]