The following article will show you how to highlight cells that have been hard coded with text which is not formula. This was the problem I was faced with recently when I had a request from a client to have a group of cells that were all formula but to give users the opportunity to over right the formula with their own text. If this were to happen the client wanted the cells to light up in a different colour to show that the cells had been altered.

The solution is rather elegant and applies condition formatting on the cell(s) you wish to have highlighted that are specifically hard coded as opposed to cells that have formula in them.

The following are the steps to follow.

The area with numbers are highlighted in the example above. These cells have formula. We want to know when the user has overwritten the formula.

Step 1 - highlight the cells for the conditional formatting.

Step 2 - choose Conditional Formatting from the Home Menu.

Step 3 - choose New Rule.

Step 4 - Choose Use a formula to determine which cells to format.

Step 5 Type the following Formula =NOT(ISFORMULA(D2))

Step 6 - Check to see that the conditional formatting works as intended.

These are the steps that should get you the result you wish. Remember the formula needs to be adjusted to the cells that you are using. A recap of the formula is as follows.