Skip to content

How to Auto Highlight on The Active Cell in Microsoft Excel

Auto Highlight Cell in Excel

When you have a lot of data in Microsoft Excel, a feature that may be very useful to you is auto-highlight on the active cell we selected. This will make it easier for us to identify data so as to minimize errors in seeing one data with another. The auto-highlight feature on the active cell is not in Microsoft Excel by default, but we can create it with a simple formula.

This auto-highlight feature will highlight cells horizontally, while the cursor where you click cells can style them to your liking. For example, you can use a certain background color, a certain font style, and so on. To create an automatic highlight feature on the active cell in Microsoft Excel you can follow the following guide.

How to Create Auto Highlight on The Active Cell in Microsoft Excel

  1. Open a sheet in Microsoft Excel, create some data as an example and select/block the data.
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 1
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 1
  1. Click “Conditional Formatting” > “New Rule“. Select the option “Use a fromula to determine which cells to format“.
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 2
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 2
  1. Enter the following formula in the formula field (select the one that works, because writing the formula depends on the installation of your Windows system, some work with semicolon “;” and some work with comma “,“).

Choose one

=OR(CELL("col")=COLUMN(),CELL("row")=ROW())

or

=OR(CELL("col")=COLUMN();CELL("row")=ROW())
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 3
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 3
  1. Click the “Format” button, click the “Fill” tab, select the color you like then click “OK“.
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 4
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 4
  1. Click “OK” to save the formula and highlight color.
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 5
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 5
  1. Repeat the steps to enter one of the following formulas as the previous step. But this formula begins with the condition “AND“. Select “Conditional Formatting” > “New Rule“, select the same option under “Use a fromula to determine which cells to format“. Select the color on the “Format” button > “Fill” with the color to be used in the active cell, here I choose yellow. If you have done please click “OK“.

Choose one

=AND(CELL("col")=COLUMN(),CELL("row")=ROW())

or

=AND(CELL("col")=COLUMN();CELL("row")=ROW())
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 6
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 6
  1. In your sheet tab click “View Code“. Select the “Worksheet” option. Enter the following code among the Private Subs. After that you can close the VBA Code.
Target.Calculate
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 7
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 7
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 8
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 8
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 9
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 9
  1. You have successfully created an automatic highlight in Microsoft Excel.
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 10
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 10
  1. To keep the VBA Code stored properly, save the file as a macro by clicking “File” > “Save As” > select “Excel Macro-Enabled Workbook (*.xlsm)“.
How to Create Auto Highlight on The Active Cell in Microsoft Excel - Step 11
How to Create Auto Highlight on The Active Cell in Microsoft Excel – Step 11
  1. You may also need to allow macros to be automatically loaded in excel settings so that the file can be opened normally at a later time, this menu is located in “Trust Center Settings” in Microsoft Excel Options.

That’s a guide on how to create an automatic highlight on the active cell in Microsoft Excel using formulas and also VBA codes. You can also see the guide in the following video.

How to Create Auto Highlight on The Active Cell in Microsoft Excel – YouTube

Leave a Reply

Your email address will not be published. Required fields are marked *