多功能条件格式化单元格范围

I want to give a quick indication using coloured cells of the time likely to be taken to complete a task. So for example:

我想使用可能用于完成任务的时间的彩色单元来快速指示。例如:

if cell B2 = 1 then fill cell range G2:H2 with light red
if cell B2 = 2 then fill cell range G2:J2 with light red
if cell B2 = 3 then fill cell range G2:L2 with light red

如果单元格B2 = 1,则填充单元格范围G2:H2,如果单元格B2 = 2则用浅红色填充单元格区域G2:如果单元格B2 = 3,则填充单元格范围G2:J2为浅红色,然后用浅红色填充单元格范围G2:L2

How might I achieve this?

我怎么能实现这个目标?

2 个解决方案

#1

Select G2:H2 and use a CF formula rule of:

选择G2:H2并使用CF公式规则:

=$B$2=1

Select G2:J2 and use a CF formula rule of:

选择G2:J2并使用CF公式规则:

=$B$2=2

Select G2:L2 and use a CF formula rule of:

选择G2:L2并使用CF公式规则:

=$B$2=3

each with the formatting of your choice.

每个都有您选择的格式。

#2

If you want a dynamic formula and only one format setting you can select all the cells you want to format, then make a new conditional formatting rule using the formula:

如果您想要动态公式并且只需要一种格式设置,则可以选择要格式化的所有单元格,然后使用以下公式创建新的条件格式设置规则:

=$B$2*2+COLUMN($G:$G)-COLUMN()>0

Which will affect 2 additional cells each time you increment $B$2. I used your examples of $B$2 and starting with $G:$G but you can change those if needed.

每次增加$ B $ 2时,这将影响2个额外的单元格。我使用了$ B $ 2的例子,从$ G:$ G开始,但你可以根据需要改变它们。

If you need multiple format settings this won't work.

如果您需要多种格式设置,则无效。

#1