In a video (http://www.youtube.com/watch?v=bGjqDGF7xaM) published on YouTube back in 2008, I explained how to highlight the active row in Excel. In this brief article, I discuss how you can highlight the active cell in Excel. In this particular case, I will only change the font color of the cell, but you can later change the conditional formatting and apply whatever formatting you wish.

Here's how it is done:

1. Create a named range called AddressOfActiveCell using the formula: =ADDRESS(5,6)
2. Select the entire worksheet and apply a conditional formatting using the formula (the active cell being A1 upon selection): =ADDRESS(ROW(A1),COLUMN(A1))=AddressOfActiveCell
3. Now, open the visual basic editor and insert the following code:


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

    With ThisWorkbook.Names("AddressOfActiveCell")
        .Name = "AddressOfActiveCell"
        .RefersToR1C1 = "=address(" & ActiveCell.Row & "," & ActiveCell.Column & ")"
    End With

End Sub

Check the video tutorial here:

Excel 2010 reinvents the conditional format for data bars. We now have more control over the conditional formatting as a whole and not only databars; however, with data bars we now have the option to plot negative values as well.

Here’s how it looks like:

Figure 1: Excel 2010 Conditional Format Data Bars

Once we apply one of the built-in conditional formats for data bars, we can access the Conditional Format Manage Rules dialog box in order to edit the new conditional format rule:

Figure 2: Determining Negative Value and Axis...


Finally, you can determine various aspects of how the value are displayed as well as the axis:

Figure 3: Determining data bars aspects such as axis settings

Excel 2007 introduced Icon Sets to Excel Conditional Formatting. Excel 2010 introduces No Cell Icon to Conditional Formatting. In Excel 2007, after applying your Icon Set Conditional Format, you would get something like this:

Figure 1: Icon Set Conditional Format

The problem, of course, is that the whole this is a mess. If the idea is to identify poor grades, you’d need to create extra Conditional Formats so that you could hide the undesired icons. Excel 2010 introduces No Cell Icon which removes the need for extra formulas and extra Conditional Formats. Here’s how it looks like:

Figure 2: No Cell Icon

By choosing No Cell Icon, Excel 2010 will omit the icons for the selected conditions saving you all the hassle of extra conditioning:

Figure 3: Result after applying No Cell Icon

Let us suppose that you have a list. In this list, you want to identify the cells that contain a certain text. Figure 1 shows the initial setup of our problem:

Figure 1 -

Figure 2 -

Figure 3 -

Figure 4 -

Figure 5 - Final step o four conditional formatting with data validation


You may also want to read this article (