Group in Excel will not highlight

I am using excel 2013.  I have set up grouping based on the project name with the applicable details below.  There is a formula in the details under the group that highlights the row to the color red which is OK.  How do I get the row that contains the group to highlight as well when the detail row below it are highlighted as well?  That when the details are hidden and the groups are just showing the group should be highlighted if any of the detail rows underneath the group are highlighted.  How is this behavior controlled?
Gary HarperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
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.
Gary HarperAuthor Commented:
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
byundtMechanical EngineerCommented:
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
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Gary HarperAuthor Commented:
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?
byundtMechanical EngineerCommented:
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
Gary HarperAuthor Commented:
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
byundtMechanical EngineerCommented:
Here are some macros to automate your data reporting. The code must be installed as indicated in three locations: a regular module sheet, ThisWorkbook code pane, and worksheet Summary code pane.

The code will run automatically when you open the workbook and when you switch to the Summary worksheet. When the code runs, it will erase the contents of the Summary worksheet, then import data on the projects that are overdue or within one week of being due for each employee.
'This sub must go in a regular module sheet
Sub Summarize()
Dim ws As Worksheet, wsSummary As Worksheet
Dim cel As Range, rg As Range
Dim vColor As Variant, vColors As Variant, vMessages As Variant
Dim i As Long, j As Long, nColor As Long, nCols As Long

Application.ScreenUpdating = False
Set wsSummary = ActiveWorkbook.Worksheets("Summary")
wsSummary.UsedRange.ClearContents
vColors = Array(3, 6)       'Specify Conditional Formatting color indexes
vMessages = Array("Projects that are overdue (RED)", "Projects that are due within one week (YELLOW)")  'Specify messages

i = 2
With wsSummary
    For Each vColor In vColors
        nColor = nColor + 1
        .Cells(i, 1).Value = vMessages(nColor - 1)
        .Cells(i, 1).Style = "Heading 2"
        .Cells(i, 2).Interior.ColorIndex = vColor
        
        For Each ws In ActiveWorkbook.Worksheets
            Select Case ws.Name
            Case .Name, "Data"     'Don't process these worksheets
            Case Else
                Set rg = ws.UsedRange
                nCols = rg.Columns.Count
                If i = 2 Then
                    .Cells(1, 1).Value = "Employee name"
                    .Cells(1, 2).Resize(1, nCols).Value = rg.Rows(1).Value
                    .Rows(1).Cells.Style = "Heading 1"
                End If
                For Each cel In rg.Columns(1).Cells
                    If cel.DisplayFormat.Interior.ColorIndex = vColor Then
                        i = i + 1
                        .Cells(i, 1).Value = ws.Name
                        .Cells(i, 2).Resize(1, nCols).Value = cel.Resize(1, nCols).Value
                    End If
                Next
            End Select
        Next
        i = i + 3
    Next
    
    nCols = .UsedRange.Columns.Count
    For j = 1 To nCols
        .Columns(j).EntireColumn.AutoFit
    Next
End With
End Sub

Open in new window

'This sub must go in ThisWorkbook code pane. It won't work at all if installed anywhere else!
Private Sub Workbook_Open()
Summarize
End Sub

Open in new window

'This sub must go in worksheet Summary code pane. It won't work at all if installed anywhere else!
Private Sub Worksheet_Activate()
Summarize
End Sub

Open in new window

Project-SummaryQ28736822.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary HarperAuthor Commented:
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.
Gary HarperAuthor Commented:
Excellent solution.  Very pleased with the results.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.