# Conditional formatting a parent cell color based on conditionally formatted child cell colors

Copper Contributor

# Conditional formatting a parent cell color based on conditionally formatted child cell colors

Good morning all.

I have 1 parent cell A1 and 3 child cells A2-A4.  The child cells are conditionally formatted to have a color if the conditions exist.  But what I want to do is to conditionally color the parent cell if 2 or more of the child cells have color.  Please advise how I might pull this off.

15 Replies

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

You'll have to create rules for A1 that combine the rules for A2:A4, and check whether two or three of those rules are met.

Without knowing any details it's hard to be more specific...

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

I set a rule for the child cells to colorize if "yes" is present in the cell.  But in the parent cell (A1) I'm struggling to identify a formula that will color A1 in red if 2 or more child cells are colored yellow.

I simplified this just for testing purposes.  In the real Excel document, the conditional formatting of the child cells being yellow is based on the top 10 or bottom 10.

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

Create a rule of type "Use a formula to determine which cells to format", with formula

=COUNTIF(A2:A4, "yes")>=2

Edited to replace . with ,

For the real situation, it would be useful to know the exact setup.

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

I get the following error when trying that formula.  I should mention that the Parent Cell would be A1, A2, A3, etc. and the child cells would be A2-A4, B2-B4, etc.  Here is an example as well as the error.

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

Sorry, child cells would be B2-D2, B3-D3 and so forth.

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

I had a typo in the formula - it should have used a comma instead of a point.

=COUNTIF(B2:D2, "Yes")>=2

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

That worked beautifully.  Thank you.

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

Ok so I think I misspoke. Does that only identify if a "yes" is in the column, or if a color is in the column. I'm trying to find one that identifies a colored column greater than equal to 2.

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

It identifies at least 2 "Yes: in the row.

I thought you had a conditional formatting rule to color cells with "Yes".

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

@HansVogelaar  Yes, I have conditional formatting for the columns to colorize cells.  The true data source is all in dollars and cents, not yes and no.  I put that together as an example.  The part I need to capture is the colored cell if it exists 2 or more times in a row, and then take the row label and change that color.  To simplify, If cell color exists & is >=2, highlight third row label cell.

Is this even possible in a simple formula?

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

Could you attach a small sample workbook demonstrating the REAL problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Conditional formatting a parent cell color based on conditionally formatted child cell colors

@HansVogelaar In the example below, everything that is red bold was highlighted yellow in the original using conditional formatting - Top 5 or bottom 5 (for negative amounts).  The formatting for cell color didn't port over.  If these bolded red data cell rows contain 2 or more instances of bolded data, I want to change the background color of the employee name in that row to red.

 Employee Name SI Amt Amt (1) Amt (2) Amt (3) Amt (4) NS Amt Sus Amt Name 1 -189.79 -71.43 -40.90 105.90 5.50 22 5 Name 2 -67.96 -11.35 -8.00 26.58 0.00 2 0 Name 3 306.33 -142.75 -6.20 354.22 21.05 8 6 Name 4 928.70 -273.18 -4.00 205.08 0.00 8 5 Name 5 -198.72 -16.25 -20.00 40.15 1.70 9 0 Name 6 -200.84 -101.84 -19.90 131.30 12.23 11 5 Name 7 -289.22 -42.77 0.00 71.03 8.38 0 3 Name 8 -40.35 -170.99 -32.95 286.65 1.30 24 5 Name 9 -755.99 -162.28 -123.45 123.03 3.00 1 7 Name 10 -865.19 -36.84 -43.95 39.30 2.90 16 6 Name 11 139.17 -51.33 -22.00 14.76 1.50 7 2 Name 12 -259.18 -80.68 -6.40 32.48 10.75 5 1 Name 13 -375.59 -165.01 -16.60 88.39 2.25 3 6 Name 14 132.85 -55.25 -11.00 24.55 10.50 21 2 Name 15 -85.14 -474.47 -25.71 87.71 4.00 6 2 Name 16 562.19 -80.45 -65.95 131.53 1.00 11 5 Name 17 -100.77 -10.35 -27.00 29.76 0.00 26 2 Name 18 -64.77 -163.45 -13.05 298.18 21.40 12 8 Name 19 278.72 -31.80 -7.95 169.41 32.95 4 3 Name 20 -495.65 -23.07 0.00 16.40 6.60 1 3