Forum Discussion
Solved
If cell(s) have conditional format (fill), then do something, else input value
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).
- 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)
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)
- 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)
