Excel Alternating Rows Conditional Formatting Example

Problem
Need to format groups of matching rows with alternating colors.

Solution
Use a formula with conditional formatting.

1) Decide which column will be the "key column".
2) Sort sheet by key column.
3) Define a new Conditional Formatting column

• Put "1" in column header row
• Put formula in second row. Example: "=IF(A2=A1,D1,D1*-1)" where "A" is key column and "D" is Conditional Formatting column.
• Copy formula into each row of column.
  
  

4) Define Conditional Formatting for alternating rows

• Select all active cells in sheet
• Click Conditional Formatting/New Rule...
• Select Rule Type "Use a formula to determine which cells to format".
• In "Format values where this formula is true:", define the Conditional Formatting column where = 1: Example: "=$D1=1" where "D" is Conditional Formatting column.
  
  
  
  
• Click Format...
• Select Fill tab, pick color, click OK, click OK.