I need to mark the changed cells in a new version of an Excel file. Using the Compare files functionality of Excel 2013, I can get a list of changed cells:
我需要在新版本的Excel文件中标记更改的单元格。使用Excel 2013的比较文件功能,我可以获得已更改单元格的列表:
Now I'd like to use that list of cell references to mark the corresponding cells in the Excel sheet through conditional formatting.
现在,我想使用该单元格引用列表通过条件格式标记Excel工作表中的相应单元格。
How do I do that? (There are hundreds of cell references, so I do not want to do it manually.)
我怎么做? (有数百个单元格引用,所以我不想手动执行。)
1 个解决方案
#1
3
You could try putting this as a formula into Conditional Formatting:-
您可以尝试将此作为公式放入条件格式: -
=NOT(ISERROR(MATCH(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),Changes,0)))
where Changes is the range where you have your list of cells that don't match. I'm assuming that your list of changes do not have $ signs in them.
其中,更改是您的单元格列表不匹配的范围。我假设您的更改列表中没有$符号。
Here is a much more concise version of the formula as explained in the comment below.
这是一个更简洁的公式版本,如下面的评论中所述。
=MATCH(ADDRESS(ROW(),COLUMN(),4),changes,0)
#1
3
You could try putting this as a formula into Conditional Formatting:-
您可以尝试将此作为公式放入条件格式: -
=NOT(ISERROR(MATCH(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),Changes,0)))
where Changes is the range where you have your list of cells that don't match. I'm assuming that your list of changes do not have $ signs in them.
其中,更改是您的单元格列表不匹配的范围。我假设您的更改列表中没有$符号。
Here is a much more concise version of the formula as explained in the comment below.
这是一个更简洁的公式版本,如下面的评论中所述。
=MATCH(ADDRESS(ROW(),COLUMN(),4),changes,0)