Conditional Formatting - Alternating Groups with some blank cells

kopp123

New Member
Joined
Apr 7, 2014
Messages
9
I have a report that exports from a system in the following format:

ProjectProject NameDeadlinePerson AssignedPerson Role
0045Red Project1/1/2015Jane DoeManager
Pat RogersApprovals
0092Project Blue1/15/2015Jane DoeManager
Sue GreenCoordinator
Chuck SmithCoordinator
Pat RogersApprovals
0055Purple Account2/1/2015Jane DoeManager
Chuck SmithCoordinator
Pat RogersApprovals

<tbody>
</tbody>


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

ProjectProject NameDeadlinePerson AssignedPerson Role
0045Yellow Project1/1/2015Jane DoeManager



Pat RogersApprovals
0092Project Blue1/15/2015Jane DoeManager
Sue GreenCoordinator
Chuck SmithCoordinator
Pat RogersApprovals
0055Purple Account2/1/2015Jane DoeManager



Chuck SmithCoordinator



Pat RogersApprovals

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Apr 7, 2014
Messages
9
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!!


ABCDEFG
1ProjectProject NameDeadlinePerson AssignedPerson RoleNote DateNote
20045Red Project1/1/2015Jane DoeManager10/1/2014Project Active
3Pat RogersApprovals10/15/2014Project on Hold
410/18/2014Project Active
50092Project Blue1/15/2015Jane DoeManager9/1/2014Project Active
6Sue GreenCoordinator
7Chuck SmithCoordinator
8Pat RogersApprovals
90055Purple Account2/1/2015Jane DoeManager12/1/2014Project Active
10Chuck SmithCoordinator
11Pat RogersApprovals

<tbody>
</tbody>

EDIT: The following formula seems to work for me:

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

Watch MrExcel Video

Forum statistics

Threads
1,127,583
Messages
5,625,623
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top