SOLVED

Conditional Formatting

Copper Contributor

Is it possible to utilize conditional formatting rules on a cell range where the values are being calculated with formulas?

4 Replies

@CWsChim 

Yes. In fact, it works just the same as for a cell range containing constant values.

@Hans Vogelaar 

Perhaps I am not utilizing conditional formatting correctly then because I am striking out on how to make it work. Using the attached file can you help me format the rating in cells C2:C15 & A2:A15 to highlight according to the colors in cells H5:H9?

best response confirmed by CWsChim (Copper Contributor)
Solution

@CWsChim 

Problem #1: The formula in C2:C15

 

=IF(B2<B23,"1",IF(AND(B2>=B23,B2<C23),"2",IF(AND(B2>=C23,B2<D23),"3",IF(AND(B2>=D23,B2<E23),"4",IF(B2>E23,"5")))))

 

returns text values, not numbers, because you enclose 1 etc. in quotes: "1". Change it it to

 

=IF(B2<B23,1,IF(AND(B2>=B23,B2<C23),2,IF(AND(B2>=C23,B2<D23),3,IF(AND(B2>=D23,B2<E23),4,IF(B2>E23,5)))))

 

Many of the conditions are superfluous, you can shorten the formula to

 

=IF(B2<B23,1,IF(B2<C23,2,IF(B2<D23,3,IF(B2<E23,4,5))))

 

Problem #2: the conditional formatting formula should be

 

=$C2=5

 

and similar for the other values. See the attached version (I used a different color for 2, but you can change that).

@Hans Vogelaar 

Thank you for your help with this. The version you supplied of my example sheet has some really good alternative information in here and is very helpful! I appreciate your knowledge and assistance!

1 best response

Accepted Solutions
best response confirmed by CWsChim (Copper Contributor)
Solution

@CWsChim 

Problem #1: The formula in C2:C15

 

=IF(B2<B23,"1",IF(AND(B2>=B23,B2<C23),"2",IF(AND(B2>=C23,B2<D23),"3",IF(AND(B2>=D23,B2<E23),"4",IF(B2>E23,"5")))))

 

returns text values, not numbers, because you enclose 1 etc. in quotes: "1". Change it it to

 

=IF(B2<B23,1,IF(AND(B2>=B23,B2<C23),2,IF(AND(B2>=C23,B2<D23),3,IF(AND(B2>=D23,B2<E23),4,IF(B2>E23,5)))))

 

Many of the conditions are superfluous, you can shorten the formula to

 

=IF(B2<B23,1,IF(B2<C23,2,IF(B2<D23,3,IF(B2<E23,4,5))))

 

Problem #2: the conditional formatting formula should be

 

=$C2=5

 

and similar for the other values. See the attached version (I used a different color for 2, but you can change that).

View solution in original post