SOLVED

If cell(s) have conditional format (fill), then do something, else input value

Copper Contributor

What formula can I use to look at two columns of data and if any of the columns (one or both) have conditional formatting of color fill, then highlight the cell yellow (or input "Look at!"), else input a value (such as 39)?

 

See attached. I need a formula in column S. It needs to look at both columns K and L; if either of them (or both) have conditional formatting of red fill, then input "Look at!" or, better yet, highlight the column S cell yellow, otherwise input S1 (which is 39).

 

2 Replies

The following formula does not work since it's looking at the empty contents in column L and thinking they're zero value!?

=IF(OR(K5>$K$3,L5<$L$3),"Input correct #",$S$1)

 

best response confirmed by TeresaSmags (Copper Contributor)
Solution
most simply maybe:
=IF(OR(K5>$K$3,AND(L5>0,L5<$L$3)),"Input correct #",$S$1)
BTW you can do AND and OR using * and +
=IF((K5>$K$3)+(L5>0)*(L5<$L$3),"Input correct #",$S$1)
1 best response

Accepted Solutions
best response confirmed by TeresaSmags (Copper Contributor)
Solution
most simply maybe:
=IF(OR(K5>$K$3,AND(L5>0,L5<$L$3)),"Input correct #",$S$1)
BTW you can do AND and OR using * and +
=IF((K5>$K$3)+(L5>0)*(L5<$L$3),"Input correct #",$S$1)

View solution in original post