[Solved] Conditional formatting (Need highlight county names)

[MISAJ]

Hello All,

Need help with highlighting county names

i have data available in sheet 1 (around 50 County names) and in sheet 2 i have few counties which will match with sheet 1 and whenever i type the sheet 2 county names in sheet 1 that name or row should highlight in sheet county row only

 

[shamimarita]

Hello Misaj,

To highlight the country name of sheet 1 if it exists in sheet2, you can follow these steps:

• Go to Sheet 1 and select the range (select the range A2:A90 ).
• Click on the Home tab >> from Conditional Formatting >> select New Rule.
• In the New Formatting Rule window, select Use a formula to determine which cells to format.
• In the formula box, enter the following formula:
  • =ISNUMBER(MATCH(A2, Sheet2!A:A, 0))
• Here, A2 is the first cell of the column. The formula checks if the county name in Column A of Sheet 1 exists anywhere in Column A of Sheet2.
• Click on Format, choose your desired formatting (e.g., a yellow background color to highlight the matches), and press OK.
• Press OK again to apply the rule.

 

[MISAJ]

Thank you.

Taken same formula and cells are highlighting but not highlighting the same cell going to the above cell or previous cell

If i type Lake (which is available in sheet 2) above line (India) is highlighting

 

[shamimarita]

The problem of not highlighting the same cell going to the above or previous cell occurs when your cell reference is not aligned with your dataset.

Adjust your formula:
=ISNUMBER(MATCH(A2, Sheet2!A:A, 0))
Try with:
=ISNUMBER(MATCH(A1, Sheet2!A:A, 0))
=ISNUMBER(MATCH(A3, Sheet2!A:A, 0))
Based on which cell your country name starts,

 

[MISAJ]

The problem of not highlighting the same cell going to the above or previous cell occurs when your cell reference is not aligned with your dataset.

Adjust your formula:
=ISNUMBER(MATCH(A2, Sheet2!A:A, 0))
Try with:
=ISNUMBER(MATCH(A1, Sheet2!A:A, 0))
=ISNUMBER(MATCH(A3, Sheet2!A:A, 0))
Based on which cell your country name starts,

Thank you for the help

 

[shamimarita]

You are most welcome. Keep contributing to the ExcelDemy forum by helping each other.