1
Excel 2016.
I have two cell ranges in two different sheets: sheet1!B2:B250, and sheet2!B2:B164 for customers names.
When I insert a cell in between the cell range in any sheet and type a name e.g. "George", I want that cell highlighted if "George" already exists in those ranges. Please note that there are empty cells in both ranges that I don't want highlighted.
geotso
Posted 2018-01-18T10:38:25.453
Reputation: 11
0
You can use COUNTIF function to create Conditional Formatting to format cells, please follow my steps and check if it will help you.
For Sheet1!B2:B250 please use this formula:
=COUNTIF(Sheet2!$B$2:$B$164,B2)>0
Select the range Sheet1!B2:B250 and go to Home->> Conditional Formatting->> NEw Rule...->> Please select Use a Formula to determine which cells to format->> type the formula->> Format cells.
For Sheet2!B2:B164 please use this formula:
=COUNTIF(Sheet1!$B$2:$B$250,B2)>0
The same steps as Sheet1:
Lee
Posted 2018-01-18T10:38:25.453
Reputation: 1 382
It only works for the existing cells in those ranges. As I've said I want to insert a new cell in any of the ranges and type there a name. – geotso – 2018-01-23T13:38:49.783