Dbfox947

Something different on theSmallman today - actually something different all together. Since starting my Excel website and subsequent blog over 3 and a half years ago I have created over 300 pages from financial modelling to dashboard design to unlocking Excel's scripting dictionary. I have writen every word myself and have drawn extensively on my posts on Ozgrid and Chandoo forums. Today for the first time someone else is going to do the instructing. The two handsome gentleman above are Kasper and Mikel. They came to my attention through a Linkedin feed from Modeloff, the financial modelling world championships. They hail from Denmark - a place Australians feel a close connection with as one of our own is due to become Queen of Denmark one day. Kasper and Mikel have created an Excel blog which draws on their passion and knowledge of Microsoft Excel.

On Spreadsheeto - these gentleman have a bold ambition - 'create the best information about Excel you have ever read.' While this is an ambitious exercise, the guys have made a very strong start, posting countless useful blog posts on all things Excel. I wish them every success as they build their brand and Excel consulting business.

AUTO-HIGHLIGHT THE ACTIVE ROW WHEN A CELL IS SELECTED

In this guide, you will learn how to highlight a row automatically when you (or someone else) selects a cell in a sheet. This way it is always easy for the user to see which row is selected. This is helpful in spreadsheets that are set up with vertical entries – like a database. Be aware that this method requires some processing power, so if your file is already having trouble running smoothly, then this might not be a sustainable feature for you.

• Highlighting the Entire Row and Column that Contain the Active Cell The following code example clears the color in all the cells on the worksheet by setting the ColorIndex property equal to 0, and then highlights the entire row and column that contain the active cell by using the EntireRow and EntireColumn properties.
• The formula will be ‘=Row (a1)=HighlightRow’ – where “HighlightRow” is the name of the defined range in Step 1. Then click the format button. In the format cells window, switch to the fill tab, and choose the color you want to use as the color to highlight the active row.

Here are the steps to search and highlight all the cells that have the matching text: Select the dataset on which you want to apply Conditional Formatting (A4:F19 in this example). Click the Home tab. In the Styles group, click on Conditional Formatting. In the drop-down options, click on New Rule. In the ‘New Formatting Rule’ dialog box.

In this article and examples, I use Excel 2016 for Windows, but this method is also usable for you if you are running Excel 2007/2010/2013.

The General Idea

In order for this to work, we need two things:To set up a conditional formatting rule that highlights an entire row if a certain formula is true.Write a macro that recalculates the selected cell(s) when a new selection is made.These two things are fairly straightforward. I will show you how in the following.If you want to tag along as you progress in this guide, please download the project file here.

Highlighting rows with conditional formatting in itself is not difficult. It is the automatic part that is tricky. Check out this guide to conditional formatting if you are not up to speed with the fundamentals.

First, select all continuous data by selecting a cell in your data and using the shortcut ‘Ctrl + A’. In the project file, the selected range is A1:E55.

Then, click the ‘Conditional Formatting’-button on the ‘Home’ tab in the ribbon.

Click ‘New Rule’ and in the following dialog box choose ‘Use a formula to determine which cells to format’.

In the formula field, enter this formula:

=OR(CELL('row')=CELL('row',A1))

The last argument of the second ‘CELL’-function (the A1) must be the top left cell of the selected data (from before you clicked the ‘Conditional Formatting’ button).

Customizing the Format

It is up to you whether the row is highlighted by bolding the font, changing the fill color or something entirely different. A word of advice, though: Please don’t make the row stand out too much. If you color the row blood-red people will have a difficult time reading the values in the row. It will then be tiresome and hard to find the corresponding values in the sheet, which goes against the original purpose of the feature.

Customize your format by clicking the ‘Format…’ button in the ‘New Formatting Rule’ dialog box.

From the tabs at the top of the dialog box, choose which kind of formatting you want to apply. For this example, I have chosen a yellow fill for the cells in the row. Then click ‘OK’.

Writing the Macro

I have written the macro for you, but it is not complicated. It simply looks like this:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub

This code goes not in a normal module, but in the code belonging to a specific worksheet. Use the shortcut ‘Alt + F11’ to get to the ‘VBA editor’.

Double click ‘Sheet2(Employees)’ in the ‘Project Explorer’ and paste the code from above. If your ‘Project Explorer’ (the menu to the left) is missing and it does not look like the screenshot on your computer, you need to toggle on the ‘Project Explorer’. Click ‘View’ in the menu and click ‘Project Explorer’.

Conclusion

There you have it. Setting this up is quite easy and quick to do, but adds a nice touch to the system you have created if it holds data in a database form. Test it out for yourself.

Highlight Current Row In Excel For Mac Download

If you haven’t downloaded the project file, be sure to save your work as a macro-enabled workbook (The file type ending with .xlsm).

This guide is written by Kasper from Spreadsheeto.

Freeze Top Row | Unfreeze Panes | Freeze First Column | Freeze Rows | Freeze Columns | Freeze Cells | Magic Freeze Button

If you have a large table of data in Excel, it can be useful to freeze rows or columns. This way you can keep rows or columns visible while scrolling through the rest of the worksheet.

Freeze Top Row

To freeze the top row, execute the following steps.

1. On the View tab, in the Window group, click Freeze Panes.

2. Click Freeze Top Row.

3. Scroll down to the rest of the worksheet.

Result. Excel automatically adds a dark grey horizontal line to indicate that the top row is frozen.

Unfreeze Panes

To unlock all rows and columns, execute the following steps.

1. On the View tab, in the Window group, click Freeze Panes.

Highlight Current Row In Excel For Mac

2. Click Unfreeze Panes.

Freeze First Column

To freeze the first column, execute the following steps.

1. On the View tab, in the Window group, click Freeze Panes.

2. Click Freeze First Column.

3. Scroll to the right of the worksheet.

Result. Excel automatically adds a dark grey vertical line to indicate that the first column is frozen.

Freeze Rows

To freeze rows, execute the following steps.

1. For example, select row 4.

2. On the View tab, in the Window group, click Freeze Panes.

3. Click Freeze Panes.

4. Scroll down to the rest of the worksheet.

Result. All rows above row 4 are frozen. Excel automatically adds a dark grey horizontal line to indicate that the first three rows are frozen.

Freeze Columns

To freeze columns, execute the following steps.

Highlight Active Row In Excel Macro

1. For example, select column E.

2. On the View tab, in the Window group, click Freeze Panes.

3. Click Freeze Panes.

4. Scroll to the right of the worksheet.

Result. All columns to the left of column E are frozen. Excel automatically adds a dark grey vertical line to indicate that the first four columns are frozen.

Freeze Cells

To freeze cells, execute the following steps.

1. For example, select cell C3.

2. On the View tab, in the Window group, click Freeze Panes.

3. Click Freeze Panes.

4. Scroll down and to the right.

Result. The orange region above row 3 and to the left of column C is frozen.

Magic Freeze Button

Add the magic Freeze button to the Quick Access Toolbar to freeze the top row, the first column, rows, columns or cells with a single click.

1. Click the down arrow.

2. Click More Commands.

3. Under Choose commands from, select Commands Not in the Ribbon.

4. Select Freeze Panes and click Add.

5. Click OK.

6. To freeze the top row, select row 2 and click the magic Freeze button.

7. Scroll down to the rest of the worksheet.

Result. Excel automatically adds a dark grey horizontal line to indicate that the top row is frozen.

Note: to unlock all rows and columns, click the Freeze button again. To freeze the first 4 columns, select column E (the fifth column) and click the magic Freeze button, etc.