I'm unable to create a rule in Conditional Formatting for this requirement. I was tried using this option "Use a formula to determine which cells to format" inside the rule but didnt get proper formulae.
我无法在条件格式中为此要求创建规则。我尝试使用此选项“使用公式来确定规则中要格式化的单元格”,但没有得到正确的公式。
My Requirement:
If I change the Activity value to Completed in column A then accordingly the font color should be in (Sky blue) and font size is (10) in columns B and C.
如果我在A列中将活动值更改为已完成,则相应的字体颜色应为(天蓝色),字体大小为(10),列为B和C.
If I change the activity value to Delayed in column A then the font color should be in (Red) and font size is (Default or no change) in columns B and C.
如果我将活动值更改为A列中的延迟,则字体颜色应为(红色),字体大小为(默认或无更改)列B和C.
Also if I manually type to change the Activity type from To Do to Completed then the Final Date column field value should be automatically filled with the current or Today's date which is as on date.
此外,如果我手动键入以将活动类型从待办事项更改为已完成,则最终日期列字段值应自动填充当前日期或今天的日期。
What formulae I can use for this requirement? How?
我可以用什么公式来满足这个要求?怎么样?
1 个解决方案
#1
Changing the text format is easy to solve with Conditional Formatting, changing the cell's text to today's date requires VBA.
使用条件格式更改文本格式很容易,将单元格的文本更改为今天的日期需要VBA。
1. Text format
1.文字格式
- Select cells B2:C10
-
Create a conditional format with "Use a formula to determine which cells to format". Enter this formula:
使用“使用公式确定要格式化的单元格”创建条件格式。输入以下公式:
=$A2="Completed" -
Click the 'Format' button and apply the blue text color and your desired font size.
单击“格式”按钮并应用蓝色文本颜色和所需的字体大小。
-
Repeat the steps using this formula:
使用以下公式重复这些步骤:
=$A2="Delayed" -
Apply the red font color.
应用红色字体颜色。
选择单元格B2:C10
2. Enter today's date via VBA
2.通过VBA输入今天的日期
- Open the VBA editor with Alt+F11
- Double click the worksheet on the left hand panel where your Activity table is placed (i.e. Sheet1).
-
Enter this code in the right hand code panel:
在右侧代码面板中输入以下代码:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Value2 = "Completed" Then Cells(Target.Row, 3).Value = Date End If End Sub
使用Alt + F11打开VBA编辑器
双击左侧面板上放置Activity表的工作表(即Sheet1)。
#1
Changing the text format is easy to solve with Conditional Formatting, changing the cell's text to today's date requires VBA.
使用条件格式更改文本格式很容易,将单元格的文本更改为今天的日期需要VBA。
1. Text format
1.文字格式
- Select cells B2:C10
-
Create a conditional format with "Use a formula to determine which cells to format". Enter this formula:
使用“使用公式确定要格式化的单元格”创建条件格式。输入以下公式:
=$A2="Completed" -
Click the 'Format' button and apply the blue text color and your desired font size.
单击“格式”按钮并应用蓝色文本颜色和所需的字体大小。
-
Repeat the steps using this formula:
使用以下公式重复这些步骤:
=$A2="Delayed" -
Apply the red font color.
应用红色字体颜色。
选择单元格B2:C10
2. Enter today's date via VBA
2.通过VBA输入今天的日期
- Open the VBA editor with Alt+F11
- Double click the worksheet on the left hand panel where your Activity table is placed (i.e. Sheet1).
-
Enter this code in the right hand code panel:
在右侧代码面板中输入以下代码:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 And Target.Value2 = "Completed" Then Cells(Target.Row, 3).Value = Date End If End Sub
使用Alt + F11打开VBA编辑器
双击左侧面板上放置Activity表的工作表(即Sheet1)。