有条件地根据列中的值格式化一个单元格

时间:2022-04-14 01:37:30

The user enters a date/time into cell B2. If this matches one of the values in column L I would like to format cell B2 as red, else format cell B2 as green.

用户在单元格B2中输入日期/时间。如果这与列L中的一个值匹配,我想将单元格B2格式化为红色,否则将单元格B2格式化为绿色。

有条件地根据列中的值格式化一个单元格

Any idea how to do this?

知道怎么做吗?

3 个解决方案

#1


3  

Please format B2 green with standard fill, then apply the following Use a formula to determine which cells to format, Format values where this formula is true:

请使用标准填充格式化B2绿色,然后应用以下内容使用公式确定要格式化的单元格,格式化此公式为真的值:

=match(B2,L:L,0)>0  

with formatting (red) to suit and Applies to B2.

格式化(红色)以适应和适用于B2。

#2


1  

Assuming you are using Excel 2007 onwards:

假设您使用的是Excel 2007以上版本:

  1. Set cell shading for B2 = Green
  2. 设置B2 =绿色的单元格着色
  3. Select Cell B2 and select the Conditional Formatting > New Rule menu item
  4. 选择Cell B2并选择Conditional Formatting> New Rule菜单项
  5. Use a formula to determine which cells to format
  6. 使用公式确定要格式化的单元格
  7. In the formula bar, put in =ISNUMBER(MATCH(B2,$L$6:$L$100,0))
  8. 在公式栏中,输入= ISNUMBER(匹配(B2,$ L $ 6:$ L $ 100,0))
  9. For format, change shading to Red
  10. 对于格式,将着色更改为红色

Obviously change the $L$6:$L$100 to suit you, but that should do it...

显然要改变$ L $ 6:$ L $ 100以适应你,但是应该这样做......

#3


1  

What about breaking this up into two steps.

如何将其分为两步。

Try putting a simple formula in Cell B1:

尝试在Cell B1中添加一个简单的公式:

=ISNUMBER(MATCH(B2, L:L, 0))

= ISNUMBER(匹配(B2,L:L,0))

This will return a TRUE if there's a match and a FALSE if there is not a match. Then make two conditional rules based on Cell B1.

如果匹配则返回TRUE,如果不匹配则返回FALSE。然后基于Cell B1制作两个条件规则。

#1


3  

Please format B2 green with standard fill, then apply the following Use a formula to determine which cells to format, Format values where this formula is true:

请使用标准填充格式化B2绿色,然后应用以下内容使用公式确定要格式化的单元格,格式化此公式为真的值:

=match(B2,L:L,0)>0  

with formatting (red) to suit and Applies to B2.

格式化(红色)以适应和适用于B2。

#2


1  

Assuming you are using Excel 2007 onwards:

假设您使用的是Excel 2007以上版本:

  1. Set cell shading for B2 = Green
  2. 设置B2 =绿色的单元格着色
  3. Select Cell B2 and select the Conditional Formatting > New Rule menu item
  4. 选择Cell B2并选择Conditional Formatting> New Rule菜单项
  5. Use a formula to determine which cells to format
  6. 使用公式确定要格式化的单元格
  7. In the formula bar, put in =ISNUMBER(MATCH(B2,$L$6:$L$100,0))
  8. 在公式栏中,输入= ISNUMBER(匹配(B2,$ L $ 6:$ L $ 100,0))
  9. For format, change shading to Red
  10. 对于格式,将着色更改为红色

Obviously change the $L$6:$L$100 to suit you, but that should do it...

显然要改变$ L $ 6:$ L $ 100以适应你,但是应该这样做......

#3


1  

What about breaking this up into two steps.

如何将其分为两步。

Try putting a simple formula in Cell B1:

尝试在Cell B1中添加一个简单的公式:

=ISNUMBER(MATCH(B2, L:L, 0))

= ISNUMBER(匹配(B2,L:L,0))

This will return a TRUE if there's a match and a FALSE if there is not a match. Then make two conditional rules based on Cell B1.

如果匹配则返回TRUE,如果不匹配则返回FALSE。然后基于Cell B1制作两个条件规则。