Change cell color to match the color of another cell in its row

[yysilbs]

I am using a sheet to track contracts. Each contract is its own row with info about it organized in columns (effective date, expiration date, signer, etc.). If there is something of major concern about a contract, I color the corresponding cell red (for example - if a contract has expired or if it's missing a signature) but if there is an issue but it is not as major, I color the cell orange.

I want there to be a way to indicate in the first column (which is the counterparty name column) that one of the cells in its row is colored red or orange - by having the cell in the first column become red if there is a red cell in its row and orange if there's an orange cell in its row. Can I do this with conditional formatting? What's the simplest way to do this?

[6StringJazzer]

The only way to do this is with a macro. If you can attach your file to demonstrate the format, with some sample data (but you should probably use fake data), I can help.

What happens if the same row has a red and an orange?

[yysilbs]

If both colors are in a row, it should be red - i.e. the color of the biggest issue in that row.

[6StringJazzer]

Perhaps you missed the line where I asked you to attach a sample file.

[yysilbs]

Has the file I attached helped?

[yysilbs]

I am unsure how to attach a file. Hope this works

[6StringJazzer]

Your cells are colored in a combination of conditional formatting and permanent formatting. This uses a UDF along with conditional formatting. The UDF returns a string that tells you the highest priority shading in a range (High for red, Medium for orange) and then conditional formatting sets the fill color based on that.

Format changes do not cause a VBA event so the function is Volatile. It will update whenever there is a change to data. If you are changing formatting but not data you can force a formatting update by double clicking a cell and then hitting ENTER.

[yysilbs]

thanks!

greatly appreciate it