Excel - How do I highlight a cell if its address is stored in a column?

Question

I have a worksheet with a list of 'Special Cells' in a column, like 'C352', 'E11', etc.

How do I write a conditional formatting rule that looks through the column on the other worksheet, and if that cell is in the column, color it a certain way? I know how to do the worksheet reference and the coloring, just not how to check it the cell reference is in the column.

Answer 1

Use this as the conditional format formula

=MATCH(ADDRESS(ROW(),COLUMN(),4,1),AddressList!$A:$A,0)

Where the sheet and column containing addresses is sheet AddressList, column A

How it works

• ROW() returns the row of the cell containing the formula (either in the cell itself, or in conditional formatting on the cell
• COLUMN() ditto for column
• ADDRESS( r, c, 4, 1) returns the address or row r, column c. 4 means relative (ie no $'s. 1 means A1 style. So ADDRESS(ROW(),COLUMN(),4,1) will be the address of the conditionally formatted cell in A1 notation
• MATCH(val, AddressList!$A:$A, 0) searches for val in range AddressList!$A:$A, 0 means exact match. Returns row number if val is found, #N/A if not.
• Conditional format treats a possitive number as True and #N/A as false

Answer 2

Based on your other question, I'd use a formula like:

=IF(SUBSTITUTE(CELL("address",INDIRECT($C$1)),"$",)=SUBSTITUTE(CELL("address",D4),"$",),1,0)

in your conditional format. This will format D4 (or whatever other cells you apply the conditional format to) based on whether C1 has a "D4" in it.