Conditional Formatting

Topic

New Reply

I would like to do a couple of things in Excel.

1. format the font colour of alternate rows in two different colours
2. When the value of cell N2 is “Complete” I want the whole row to be shaded ( A2 to N2)
3. Cells D2 E2 have dates in them and I want them to be shaded red if the date is today or later but NOT if the N2 is “Complete”

Is this possible in Conditional formatting or is there a better option?

Reply | Quote

Replies

The attached workbook has three rules. I have captured these in screen shots for you.

If you need this example done in an earlier version of Excel then let us know, or see if you can create similar rules yourself.

Reply | Quote

I assumed that the formatting should be applied to all rows, not just row 2.

1. Select columns A:C. A1 should be the active cell.
Select Tools | Conditional Formatting…
Select Formula Is from the first dropdown.
Enter the formula

=$N1=”Complete”

Click Format…
Activate the Pattern tab.
Select the shade you want to use.
Click OK.
Click Add >>
Select Formula Is from the first dropdown.
Enter the formula

=MOD(ROW(),2)=0

Click Format…
Activate the Pattern tab.
Select the shade you want to use.
OK your way out.

2. Select columns D:E. D1 should be the active cell.
Select Tools | Conditional Formatting…
Select Formula Is from the first dropdown.
Enter the formula

=$N1=”Complete”

Click Format…
Activate the Pattern tab.
Select the shade you want to use.
Click OK.
Click Add >>
Select Formula Is from the first dropdown.
Enter the formula

=D1>=TODAY()

Click Format…
Activate the Pattern tab.
Select the shade you want to use.
Click OK.
Click Add >>
Select Formula Is from the first dropdown.
Enter the formula

=MOD(ROW(),2)=0

Click Format…
Activate the Pattern tab.
Select the shade you want to use.
OK your way out.
(You may want to remove the second condition from D1 and E1)

3. Select columns F:N. F1 should be the active cell.
Perform the same steps as under 1.

4. Select from column O to the right.
Select Tools | Conditional Formatting…
Select Formula Is from the first dropdown.
Enter the formula

=MOD(ROW(),2)=0

Click Format…
Activate the Pattern tab.
Select the shade you want to use.
OK your way out.

See the attached workbook.

Reply | Quote

Thankyou both for your replies. This gives me a great starting point and some thinking to do.
Much appreciated.

Reply | Quote