It is always useful to highlight important cells so I can distinguish quickly which, for instance, problem is more important than the others because it is classified by P1, P2, P3, etc. In the other hand, if I have to decide, I will use a macro, read Double click or right click to change the color of a cell using a macro in an excel report.

• 1. Select the column E “priority”
• 2. Click on “conditional formatting -> new rule”
• 3. Select “format only cells that contain” and fill the fields as shown in the picture

• 4. Click on “format”
• 5. In the “fill” tab, select the red color then click “OK”

• 6. Click “OK”
• 7. Do the same steps for P2, P3, etc. and put different color for each one of them
  NOTE: to open the rule, select any cell in the column E, if you select a cell in the column A for instance, the rule doesn’t exist.

The conditional formatting is very useful in many situations, if the default options don’t give me the results I want, I will use the “use a formula to determine which cells to format” option.

Second example, I want to have every cell in the table to have borders so each time I will add a new row, it will do it automatically.

• 1. Select all columns, for instance, the column A to E
• 2. Click on “conditional formatting -> new rule”
• 3. Select “use a formula to determine which cells to format”
• 4. In the “format values where this formula is true” field, put
  =($B1<>"")
  NOTE: I am telling it that if a cell in the column B has a value, put borders, if empty, do nothing

• 5. Click on “format”
• 6. In the “border” tab, select “outline” then click “OK”
• 7. Click “OK”
  NOTE: if there are new columns until G, open the rule and change =$A:$E by =$A:$G
  NOTE: to open the rule, select any cell in the range, if you select a cell in the column H for instance, the rule doesn’t exist.

Third example, I want to highlight a problem based on its duration. The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

• 1. On the column C “start”, select from C2 to C10
• 2. Click on “conditional formatting -> new rule”
• 3. Select “use a formula to determine which cells to format”
• 4. In the “format values where this formula is true” field, put:
  =AND(IF(D2="",NOW()-C2,D2-C2)>5,IF(D2="",NOW()-C2,D2-C2)<=100)
  NOTE: if the duration is between 6 and 100, the cell is red

• 5. Click on “format”
• 6. In the “fill” tab, select the red color then click “OK”
• 7. Click “OK”
• 8. Create a new one and put:
  =AND(IF(D2="",NOW()-C2,D2-C2)>2,IF(D2="",NOW()-C2,D2-C2)<=5)
  NOTE: if the duration is between 3 and 5, the cell is orange
• 9. And for the “fill” tab, take the orange color

• 10. Create a new one and put:
  =AND(IF(D2="",NOW()-C2,D2-C2)>0,IF(D2="",NOW()-C2,D2-C2)<=2)
  NOTE: if the duration is between 0 and 2, the cell is green
• 11. And for the “fill” tab, take the green color
  NOTE: if there are new rows until 15, open rules and change =$C$2:$C$10 by =$C$2:$C$15
  NOTE: to open rules, select any cell in the range, if you select the cell C1 or C11 or a cell in the column A for instance, rules don’t exist.
  TIPS: if I know that I will add new rows, I will put a high range for instance until 2500 so it will be =$C$2:$C$2500 instead of =$C$2:$C$10

In case if you want to highlight a color only when 2 conditions are met, use this:

=AND(B2="",D2<>"")

I am asking it to put a color only when the cell B2 is empty and D2 is not.