Conditional Formatting

Question

Hi All,

Im trying to set up a performance tracking table for a recently set up framework which has 10 number contractor members.

The table will be populated by persons from 8 various contracting authorities and used by all to track performance.

The heading contractor below (column C) contains 10 contractors names on a draw down list for Contracting authorities to select and the heading employers objective no (column E) contains the framework indicators listed 1-6 by which contractors are measured.

The Failure level 1 column (Column F) is prepopulated with the number 1 and the number 2 in a drawdown menu.

If a contractor recieves a failure level 1 the contracting authority selects 1 (I want this rows cell colour to go orange once a named contractor in column C receives a number 1 in this column - If a contractor recieves a 2nd warning the CA selects 2 (in this instance I want the cell row colour to go red)

I also want the cell row colour to go red if a contractor in column C reaches a failure level 2 (Column G - prepopulated drawdown with a yes)

All assistance greatfully accepted.

Regards,

Conor

Framework Call Off	CA	Contractor	Project	Employers Objective No	Failure level 1	Failure Level 2

Answer 1

Thanks Rich. I will try this.

Conor

Answer 2

Here is the procedure to add the Conditional Formatting rules that you described. You will need three rules, one for each of the conditions.

1. Select all of the cells that need to be evaluated for the Conditional Formatting. Since you want to format the rows, this selection would begin in A2 and go to the last row in Column G that you may need to be formatted. You can include more rows than you are using to allow for future data. Empty rows will be ignored. In the example I selected A2:G15.
2. On the Home ribbon select Conditional Formatting>New Rule>Use a Formula. Enter this formula in the Format Values line for the rule to turn the row orange if Column F has a 1.

=AND($C2<>"",$F2=1)

NOTE: The rule is set up for the selection to begin in Row 2. The first row of the selection must match the row number in the cell references in the formula. If you begin the selection in a different row than Row 2, adjust the formula to match.

3. At the lower right of the window click on Format and choose the formatting you want, in this case orange, in the Format Cells dialogue. then click OK and OK.

Repeat steps 1 through 3 with each of these formulas.

Turn row red for Column F=2

=AND($C2<>"",$F2=2)

Turn row red for Column G=Yes

=AND($C2<>"",$G2="Yes")