Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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
share|improve this answer

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.

share|improve this answer
    
This will check every cell in the worksheet to see if it's in column D on worksheet2? So if D2 = 'C42' then cell C42 would be highlighted red? –  Ehryk Sep 14 '12 at 2:21
    
For each cell you apply this conditional format to, it will highlight if its cell reference is in C1. So if you want to apply it to all cells in Column D, select Column D and enter the conditional format. You'd need to change the D4 to D1 though. –  Rhys Gibson Sep 14 '12 at 4:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.