SOLVED

# color the cells of a specific column based on the values of other columns

Occasional Contributor

# color the cells of a specific column based on the values of other columns

Hello all,

Could someone help me realize this:

I'm trying to color the cells of a specific column based on the values of three other columns.

So the three cells of the three columns contain 'yes' or 'no' values and based of the combination of these 3 values a color is chosen for the cell of another column.

6 Replies
best response confirmed by CrewReprs (Occasional Contributor)
Solution

# Re: color the cells of a specific column based on the values of other columns

``=AND(E2="yes",F2="yes",G2="yes")``

Maybe with this rule for conditional formatting. Each color requires a different rule for conditional formatting.

# Re: color the cells of a specific column based on the values of other columns

Thank you, but doesn't the '2' in 'E2' refer to a specific row in Excel? I want the formatting to apply on the value of a column (in your case 'H') for EACH row (so 1,2,3,4...). Could you help me with that? Or is this rule indeed correct for my case?

# Re: color the cells of a specific column based on the values of other columns

This is the rule for yellow color in my example.

``=AND(\$E\$1="yes",\$F\$1="yes",\$G\$1="yes")``

Perhaps i misunderstood what you want to do. Does this return your expected result? With this formula you can highlight all rows of a column if "yes" is entered in cells E1, F1 and G1. Every color requires a different rule for conditional formatting.

This is the rule for green color in my example.

``=AND(\$E\$1="yes",\$F\$1="yes",\$G\$1="no")``

# Re: color the cells of a specific column based on the values of other columns

Thank you for the response! I think you misunderstood my question. So what I want to do is color the rows of a column, let's say H, based on the values of the columns E, F and G (in your example) OF THAT ROW.
SO:
yes yes yes => yellow in that row
yes yes no => green in that row

So more like your previous answer, but I do not want to specify any row-numbers since I do not know for sure that row 59 will contain 'yes yes yes' or 'no no no' or ...
But in your answer I saw 'E2' which means you are specifying the second row? I do not want to specify any rows in my formula. Sorry I am very new to conditional formatting so it could be that your formula is perfect but I do not know how to use it correctly.

# Re: color the cells of a specific column based on the values of other columns

My first suggestion should do what you are looking for. You can lock (specify) rows and / or columns with the \$ sign. For example this =\$E\$1 would lock both column E and row 1. This =\$E1 would only lock column E.

This rule for conditional formatting doesn't specify (lock) any rows and columns and returns the expected result.

``=AND(E1="yes",F1="yes",G1="yes")``

In the attached file you can enter any combination of yes and no in columns E, F and G in any rows. Column H then is formatted according to the formatting rule (if there is a rule for this combination).

# Re: color the cells of a specific column based on the values of other columns

Thank you! It was not working because the online (latest?) version apparently requires a semicolon and not a comma for separation, so I was actually writing a slightly other formula.