HomeApplicationOffice information

How to color formula cells in Excel automatically

Formulas in Excel or functions in Excel like Vlookup and SUM functions help to calculate data or process tables more easily and quickly. And of course, when processing the data table, it will need manipulations to check operations, or some cases we need to lock Excel formulas to avoid editing. However, with long data tables, finding the correct formula cell is relatively time consuming. If so, the user can color the Excel formula cell automatically, quickly identify which data areas in the table use the formula for faster processing. The following article will guide you how to color formula cells in Excel

1. How to color formula cells in Excel automatically

At the Excel table interface to find the formula cell, we click on the Developer tab and then select Visual Basic as shown below.

How to color formula cells in Excel automatically Picture 1

Display the VBA interface, click Insert and select Module to display the code input interface.

How to color formula cells in Excel automatically Picture 2

Next, you enter the code below to filter and search for cells that use formulas in the table.Notice at the line of code Rng.Interior.ColorIndex = 36 , the number 36 is the color code so we can change to another color code if we want to use another color. Click Run to run the code.

 Sub SelectFormulaCells() 'Updateby20140827 Dim Rng As Range Dim WorkRng As Range On Error Resume Next xTitleId = 'KutoolsforExcel' Set WorkRng = Application.Selection Set WorkRng = Application.InputBox('Range', xTitleId, WorkRng.Address, Type:=8) Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas, 23) Application.ScreenUpdating = False For Each Rng In WorkRng Rng.Interior.ColorIndex = 50 Next Application.ScreenUpdating = True End Sub

Next, you localize the data in the KutoolsforExcel dialog interface and click OK to color.

How to color formula cells in Excel automatically Picture 3

The results of the cells that use the formula are colored as shown below. The color depends on the color code the user entered in the code.

How to color formula cells in Excel automatically Picture 4

2. Localize formulas in Excel

If you do not want to color in a table, just quickly highlight the range of data using the formula, just use the Go To Special tool .

We highlight the whole table and press the F5 key to open the Go To dialog box, then click Special to expand the custom.

How to color formula cells in Excel automatically Picture 5

Next to display the table, tick Formulas and click OK to identify the formula.

How to color formula cells in Excel automatically Picture 6

Then the data area using the zoned formula is identified as below.

How to color formula cells in Excel automatically Picture 7

See more:

  1. 4 basic steps for alternating coloring of lines in Microsoft Excel
  2. 4 basic steps to color alternating columns in Microsoft Excel
3.5 ★ | 2 Vote
excel formulasexcel functions

You should read it

May be interested

  • How to color the background, table color in Word, ExcelHow to color the background, table color in Word, Excel
    to present the values ​​of statistics, reports ... scientific and eye-catching, you should decorate the tables by adding more background colors to the cells in the tables. the following article helps you color the background, table color in word, excel quickly
  • Complete tutorial of Excel 2016 (Part 5): Basics of cells and rangesComplete tutorial of Excel 2016 (Part 5): Basics of cells and ranges
    whenever you work with excel, you will need to enter information - or content - into the cells . cells are the basic building blocks of a spreadsheet. you will need to learn the basics of cells and cell contents to calculate, analyze, and organize data in excel.
  • 5 useful Microsoft Excel formulas for calculating taxes5 useful Microsoft Excel formulas for calculating taxes
    you can spend big money to hire an experienced accountant handles this, or make use of excel power immediately to arrange things in order. here are five recipes that can make your life 'breathable' than in 'season' tax.
  • How to split cells, combine cells in ExcelHow to split cells, combine cells in Excel
    how to split cells, combine cells in excel. on excel spreadsheets, many times you need to split cells, merge cells to fit the data you need to enter. you can combine two or more cells together using excel's built-in functions or use visual basic to merge
  • Learn how to make Excel formulas automatically jump when entering data into the next lineLearn how to make Excel formulas automatically jump when entering data into the next line
    hello! going around the forums, there are many people looking for the trick 'excel formula automatically jumps when entering data into the next line'.
  • Change color between different lines in Microsoft ExcelChange color between different lines in Microsoft Excel
    selecting and shedding colors separately for data areas is especially useful when you have a spreadsheet with lots of different statistics. in the following article, we will show you some steps to do this in microsoft excel ...
  • Calculate by cell background color in ExcelCalculate by cell background color in Excel
    detailed instructions on how to sum data cells by cell background color in excel by building a macro function that calculates the cell background color. example: the colors have corresponding values. sum the values ​​corresponding to the cell's background color.
  • MS Excel 2003 - Lesson 5: Excel formulas and functionsMS Excel 2003 - Lesson 5: Excel formulas and functions
    formulas in excel are programs that perform calculations on data tables. these formulas perform very precise calculations such as addition, multiplication, or comparison of values ​​in a worksheet.
  • How to use formula suggestions in Excel on the webHow to use formula suggestions in Excel on the web
    formula suggestions in the newly released excel online will save you time doing simple calculations. here's how to use the formula suggestion feature in microsoft excel online.
  • How to Lock Cells in ExcelHow to Lock Cells in Excel
    locking cells in an excel spreadsheet can prevent any changes from being made to the data or formulas that reside in those particular cells. cells that are locked and protected can be unlocked at any time by the user who initially locked...

Office information