根据非空单元格填写每隔一行？

The first column in my spreadsheet comprises empty and non-empty cells. I'd like to take the first non-empty cell AND the immediate empty cells underneath it and fill their rows white. I then want to take the next non-empty cell and the immediate empty cells underneath it and fill their rows grey. I'd like to repeat this white-grey pattern throughout my spreadsheet. How can I do this without going through it manually?

我的电子表格中的第一列包含空单元格和非空单元格。我想取第一个非空单元格和它下面的直接空单元格并将它们的行填充为白色。然后，我想要获取下一个非空单元格及其下方的直接空单元格，并将其行填充为灰色。我想在整个电子表格中重复这种白灰色图案。如何在不经过手动的情况下完成此操作？

(Example: A1 is non-empty and A2:A5 are empty; I'd like all of these and their rows to have no fill (white). A6 is non-empty and A7:A9 are empty; I'd like all of these and their rows to have a grey fill.)

（示例：A1非空，A2：A5为空;我希望所有这些和它们的行都没有填充（白色）.A6非空，A7：A9是空的;我想要所有这些和他们的行有灰色填充。）

1 个解决方案

#1

You can use conditional formatting for this.

您可以使用条件格式。

Click on cell A1 then choose Conditional Formatting and then New Rule...

单击单元格A1，然后选择条件格式，然后选择新规则...

Choose the option Use a formula to determine which cells to format and then type this formula into the formula box:

选择“使用公式”选项以确定要格式化的单元格，然后在公式框中键入此公式：

= MOD(COUNTA(A$1:A1),2)=0

Click the Format... button and choose the grey color of your choice.

单击格式...按钮，然后选择您选择的灰色。

Then apply this conditional formatting to the entire A column.

然后将此条件格式应用于整个A列。

See below a working demonstration of this.

请参阅下面的工作演示。

EDIT

编辑

To apply this to entire row (instead of just first column of each row), change formula to:

要将此应用于整行（而不是每行的第一列），请将公式更改为：

= MOD(COUNTA($A$1:$A1),2)=0

#1