Formating cell colour based on a different cell's colour

[FilleFrella44]

Hello,

I'm wondering if you can change the colour of a cell based on the colour of another. The idea is to change the colour of one cell, then have several cells (which are seeds/children of the origional cell) to change to the same colour.

See below/attatched for desired end-result. I wonder if this can be done with conditional formating of any sort, or if I have to use VBA-code. And if so, can I use VBA to dynamically find the cells connected to the original?

Regards and thanks in advance,
FilleFrella

 

[6StringJazzer]

What is the logic that connect the colored number in the Sum column to which numbers should be colored in the Inputs column? Your example is actually too specific and does not give a general rule we can use to color the cells.

 

[FilleFrella44]

Thank you for the reply, sorry in advance if I've missunderstood.

The "logic" as I see it is that the inputcells are connected through a formula to a cell. When I change the colour of the "output" cell, I want all cells which have been used as input for that cell to be coloured as well.

Further, if I do a "XLOOKUP", I want only the matching cells to be highlighted, not the entire range which was used. If I use a SUMIF, then all of the summed cells should be coloured.

I'm not sure if this is possible with an "easy" solution.

 

[6StringJazzer]

The "logic" as I see it is that the inputcells are connected through a formula to a cell.

It would help if you provided the formula. It is not remotely possible to provide a solution based only on the screenshot above.

When I change the colour of the "output" cell, I want all cells which have been used as input for that cell to be coloured as well.

This will require VBA. There is no built-in Excel function that will give the you fill color of a cell.

Further, if I do a "XLOOKUP", I want only the matching cells to be highlighted

XLOOKUP returns one value, not a collection of matching cells. Can you be more specific about how you are using XLOOKUP and what results you want?

If I use a SUMIF

You seem to need a very general solution for a variety of formulas. I don't know if this is even feasible with more information about what the formulas could be. The idea is that you would need VBA to parse the formula and determine which cells are referenced. In the case of XLOOKUP, it would have to do even more than that and simulate the XLOOKUP to determine which cell gives the result. All of this sounds like a very big job to me.

 

[FilleFrella44]

"You seem to need a very general solution for a variety of formulas. I don't know if this is even feasible with more information about what the formulas could be. The idea is that you would need VBA to parse the formula and determine which cells are referenced. In the case of XLOOKUP, it would have to do even more than that and simulate the XLOOKUP to determine which cell gives the result. All of this sounds like a very big job to me."

That is sort of what I'm after. I'm thinking that the VBA code would work with similar functionallity as the Alt+M+S function producing arrows to the cells used as inputs for formulas. See attached file (=SUM() the Excel I'm using now is Swedish but a need a solution for an English Excel).

The idea is that I often have cells which uses a XLOOKUP or SUMIF in PivotTables to sum/find values. And I'm working with considerably large datasets, and hence want to highlight cells which have already been used so that I can see if any value is (1) not yet used, or (2) I can highlight a specific sum and see if all of the values contained in that sum (the input-cells) really should be in that sum.

Sorry if my explonation is a bit messy, but greatly appricaite the help thus far. Just that I'm nudged towards VBA and that the colour cannot be determined by the "normal" Excel-funcitons is a great help.

 

[6StringJazzer]

You seem to need a very general solution for a variety of formulas. I don't know if this is even feasible without more information about what the formulas could be.

Edit

 

[6StringJazzer]

Huge job. Not something I'm able to help with.