SOLVED

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

Occasional Contributor

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.

 

Thank you in advance.

 

 

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

@CrewReprs 

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

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

cf.JPG 

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?

@CrewReprs 

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.

cf.JPG

This is the rule for green color in my example.

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

   

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.

@CrewReprs 

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")

conditional formatting.JPG

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).

 

@Quadruple_Pawn 

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.