Conditional Formatting - Alternating Groups with some blank cells

[kopp123]

I have a report that exports from a system in the following format:

Project	Project Name	Deadline	Person Assigned	Person Role
0045	Red Project	1/1/2015	Jane Doe	Manager
			Pat Rogers	Approvals
0092	Project Blue	1/15/2015	Jane Doe	Manager
			Sue Green	Coordinator
			Chuck Smith	Coordinator
			Pat Rogers	Approvals
0055	Purple Account	2/1/2015	Jane Doe	Manager
			Chuck Smith	Coordinator
			Pat Rogers	Approvals

<tbody>
</tbody>

How can I apply conditional formatting to have the output show like this:

Project	Project Name	Deadline	Person Assigned	Person Role
0045	Yellow Project	1/1/2015	Jane Doe	Manager
			Pat Rogers	Approvals
0092	Project Blue	1/15/2015	Jane Doe	Manager
			Sue Green	Coordinator
			Chuck Smith	Coordinator
			Pat Rogers	Approvals
0055	Purple Account	2/1/2015	Jane Doe	Manager
			Chuck Smith	Coordinator
			Pat Rogers	Approvals

<tbody>
</tbody>

 

[Marcelo Branco]

Assuming
1. data in columns A:E beginning in row 2, headers in row 1
2. real dates in column C, not texts that look like dates

Select, for example, A2:E100 - where 100 is the last row that likely will have data in the future.

Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format

insert this formula
=AND($D2<>"",MOD(COUNT($C$2:$C2),2)<>0)
pick the format you want (Font color = red, for example)

Hope this helps

M.

 

[kopp123]

That's great. Thanks!

I do have one other bit that I didn't realize until I applied your formula. There's another column that occasionally is longer than the people assigned (see below). I'm playing around with some OR() statements now, but if you have anything that could do this as well, it would be great.

Thank you for your help!!

	A	B	C	D	E	F	G
1	Project	Project Name	Deadline	Person Assigned	Person Role	Note Date	Note
2	0045	Red Project	1/1/2015	Jane Doe	Manager	10/1/2014	Project Active
3				Pat Rogers	Approvals	10/15/2014	Project on Hold
4						10/18/2014	Project Active
5	0092	Project Blue	1/15/2015	Jane Doe	Manager	9/1/2014	Project Active
6				Sue Green	Coordinator
7				Chuck Smith	Coordinator
8				Pat Rogers	Approvals
9	0055	Purple Account	2/1/2015	Jane Doe	Manager	12/1/2014	Project Active
10				Chuck Smith	Coordinator
11				Pat Rogers	Approvals

<tbody>
</tbody>

EDIT: The following formula seems to work for me:

=AND(OR($D2<>"",$G2<>""),MOD(COUNT($C$2:$C2),2)<>0)

 

[Marcelo Branco]

That's great. Thanks!

You are welcome and thanks for the feedback

EDIT: The following formula seems to work for me:

=AND(OR($D2<>"",$G2<>""),MOD(COUNT($C$2:$C2),2)<>0)

It seems ok to me too. Good job!

M.