Group in Excel will not highlight

The highlighting is done by Conditional Formatting. If you want the group row to be highlighted the same color, you need to apply Conditional Formatting to it as well. You will need a formula criteria in the group row that tests the detail rows and results in the same highlighting.

This is one of those problems where it would be very helpful to see your actual workbook. The required formula for the group row highlighting will need to restrict itself to examining just its own detail rows. Most likely, it will need to test rows for other groups as well--but ignore them. I suspect that a SUMPRODUCT/SUMIFS/COUNTIFS formula can be devised for this purpose--but cannot make any specific suggestions without seeing the workbook and sample data.

Looking at the formula it is evaluating the entire column and the row falls into that formula.  Guess I am unclear on what is required.  Attached is a sample spreadsheet.  Please advise.
John-Doe.xlsx

I believe you are grouping by the captions in column A. There are some cells under those captions that use the ;;; Custom format to hide their ILS values--but these do not appear to be causing new groups to form.

The Conditional Formatting for the worksheet is applied in confusing fashion. I would expect every row receives the same Conditional Formatting, but that is not how it is set up. Some rows are Conditional Formatted, while others are not.

Assuming that all detail rows should have the same Conditional Formatting, then you could use formatting like the following for the caption rows. These formulas look at column E down until the next caption (4 or more characters), and apply the same date tests as are being used on the detail rows. The formulas are counting the number of rows where the condition is satisfied, so if one of them is yellow, then the caption row will be yellow. The IFERROR handles the very last group where the MATCH would otherwise fail to find another caption.

You may notice that the formulas return numeric values instead of the expected TRUE or FALSE. Conditional Formatting interprets a 0 as FALSE and any other number as TRUE.

I suggest installing the formulas in the first caption row. Then use the paintbrush icon and copy the formatting and apply it to the next caption row.
Formula for red
=COUNTIFS(OFFSET($E3,0,0,IFERROR(MATCH("????*",A4:A$2002,0)-1,2000)),"<>",OFFSET($E3,0,0,IFERROR(MATCH("????*",A4:A$2002,0)-1,2000)),"<" & TODAY())

Formula for yellow
=COUNTIFS(OFFSET($E3,0,0,IFERROR(MATCH("????*",A4:A$2002,0)-1,2000)),">=" & TODAY(),OFFSET($E3,0,0,IFERROR(MATCH("????*",A4:A$2002,0)-1,2000)),"<=" & (TODAY()+7))

Assuming that you want the same Conditional Formatting formulas everywhere in the worksheet, then you could use the formulas shown below (starting in row 3). I have applied these formulas to the attached workbook.
Formula for red
=IF(LEN($A3)<4,AND($E3<>"",$E3<TODAY()),
COUNTIFS(OFFSET($E3,0,0,IFERROR(MATCH("????*",A4:A$2002,0)-1,2000)),"<>",OFFSET($E3,0,0,IFERROR(MATCH("????*",A4:A$2002,0)-1,2000)),"<" & TODAY())>0)

Formula for yellow
=IF(LEN($A3)<4,AND($E3-TODAY()>=0,$E3-TODAY()<=7),
COUNTIFS(OFFSET($E3,0,0,IFERROR(MATCH("????*",A4:A$2002,0)-1,2000)),">=" & TODAY(),OFFSET($E3,0,0,IFERROR(MATCH("????*",A4:A$2002,0)-1,2000)),"<=" & (TODAY()+7))>0)
John-DoeQ28736822.xlsx

I didn't have much success with your approach.  I am sure it is probably an issue on my end.  My requirements have somewhat changed that due to the number of employees (worksheet name) I need to have a summary worksheet.  This summary worksheet will copy the row from my other worksheet where the row is highlighted red or yellow.  Or the summary worksheet could have one cell that contains all the values from that row that is in red and yellow.  That way by looking at the summary worksheet you would then know what specific worksheet to look at for further detail on that project and its details.  Based on what I have searched on I guess vb or a macro is required.  Am I on the right path?

You need a clearly stated logic for whether a row should be highlighted yellow or red on the detail sheets. Armed with that, you can return values to the Summary worksheet using either formulas or macro.

It will be much easier to return a row of values from the detail worksheet into a row on the Summary worksheet. Returning a row of values into a single cell is a very bad idea, in my opinion.

I suspect that a macro would be easier for you to maintain. I suggest using a Worksheet_Activate event sub that refreshes the Summary worksheet whenever you activate it.

Regardless of approach, you will need to:
1. Post a sample workbook that shows an example of what you would like
2. State the logic for coloring rows red or yellow
3. Specify which version of Excel you are using

My logic for the coloring of rows works and is as follows:

RED =AND($E2<>"",$E2<TODAY())
YELLOW =AND($E2-TODAY()>=0,$E2-TODAY()<=7)

I am using Excel 2013.  In my attached example the summary worksheet would contain the data from the other worksheets.  There could be up to ten other worksheets potentially.  Based on the highlighted rows which you can see for John Doe and Jane Plain that particular row would be copied to the summary sheet for each worksheet where applicable.  Hopefully my example makes sense.
Project-Summary.xlsx

Had time to finally review and implement.  This is totally awesome!  Exceeded my expectations and the logic was easy to follow.  I appreciate the effort and educating me.  Thank you.

Excellent solution.  Very pleased with the results.