如何在Excel中以条件格式指定相对单元格地址？ [英] How do I specify a relative cell address in a conditional format in Excel?

问题描述

如果在该行的单元格中存在日期，则有条件地将行格式化为变成黄色。当我将格式复制到下面的其他行时，它将锁定在特定的单元格上，将所有行都变成黄色。

我想要的是将单元格G1变为黄色不为null。如果G2不为空，则将第2行变为黄色。我可以手动编辑每一行的条件格式，但是如果这个条件适用于超过1000行，则必须有一个更好的方法。

我会很感激任何人都可以提供的帮助。

解决方案

没有本地公式来获取包含公式的单元格的地址。

我想我找到了在条件格式的公式中应该可能起作用的解决方案。因此，要获得包含公式的q单元格的地址，您应该输入：

  = ADDRESS（ROW（），COLUMN（）） 
  

I am conditionally formatting a row to a turn yellow if a date exists in a cell on that row. When I copy the format to other rows below, it keys on that specific cell, turning all rows yellow.

What I want is to turn row 1 yellow if cell G1 is not null. Turn row 2 yellow if G2 is not null, etc. I can manually edit the conditional format for each row, but as this condition will apply to over 1000 rows, there has to be a better way.

I would appreciate any help anyone can provide.

解决方案

I was looking through the same topic and indeed there is no native formula to get the address of the cell containing the formula.

I think I found the solution which should probably function as well in the formulas in conditional formatting. So to get the address of q cell containing the formula you should type:

=ADDRESS(ROW(),COLUMN())

这篇关于如何在Excel中以条件格式指定相对单元格地址？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！