Problem: How to highlight the wrong entries (conditional formatting).

To enter the data correctly we can use many different ways: drop-down lists, protection sheet or even macros. And you can highlight the wrong entries color to warn the user that the entered value is incorrect. 

For example we have the following form: 

And the list of departments 

If the user enters in the column “C” the department which is not contained in this list, then the cell should marked by red to hint about the error.

Select the cell C4:C6 and then on the Ribbon Tab

Home – Conditional Formatting – New Rule -“Use formula to determine which cell to format)” 

Input the following formula   =AND(NOT(ISBLANK(C4)),COUNTIF(F4:F6,C4)=0)

Functions in this article:

·         COUNTIF - formula calculates the number of cells for the value entered in cell C4 within the list of allowed departments. If this number is zero, then entered the departments in is not in the list.

·         ISBLANK function checks whether something in the cell C4.

·         Function AND checks both given conditions.