Help: Simplify this formula

[cbryan15]

Hi all! Good day. I would like to seek for help. How can I simplify my formula,

=OR($K$49:$K$58="SUN",$N$49:$N$58="SUN",$Q$49:$Q$58="SUN",$T$49:$T$58="SUN",$W$49:$W$58="SUN",$Z$49:$Z$58="SUN",$AC$49:$AC$58="SUN",$AF$49:$AF$58="SUN",$AI$49:$AI$58="SUN",)

This formula is used as conditional formatting formula of cells A49:AI58. Those given cells are the ones that might have the value SUN. If any of those cells has the value, the range cells will change its color.

I hope you can help me with this. Thanks a lot!

 

[Weazel]

so that I'm understanding correctly....
If SUN is in any cell in any of the ranges you need just that column highlighted or are you just wanting the instances of SUN highlighted?

 

[cbryan15]

so that I'm understanding correctly....
If SUN is in any cell in any of the ranges you need just that column highlighted or are you just wanting the instances of SUN highlighted?

Thank you for the reply.

If SUN is in any cell in any range, the column highlighted will change its format. Thank you very much!

 

[novel]

Does the formula provided give you the results you want or is there an issue with it?

I ask because when I tried the provided formula, the formatting would only fill part of the range and not the entire range because each cell in the range was being checked to meet the condition.

If I'm understanding the function of the formula correctly, you want the entire range (e.g., A49:A58) to be filled if one cell contains "SUN". To do this I added the following formula to the top of the sheet

=COUNTIF(K$49:K$58,"SUN")>0

I dragged it across to give the value for all the ranges. I then used that to apply the condition formatting with the rule being in this case =K$1, and writing the range it applies to as K$49:K$58. This way I could apply the conditional formatting with the paintbrush to the ranges I wanted and the formula would be dynamic (double clicking the paintbrush locks it in so you can apply the formatting without having to keep activating it).


The downside is that you will have conditional formatting rules for each of those cell ranges.

*edit: in the image A$40 had the countif formula

 

[Weazel]

you could try ...=COUNTIF($K$49:$AI$58,"sun")

ensure you A49:AI58 first, and its dependent on what is in the cells between the criteria ranges

 

[cbryan15]

thanks guys! that solved this problem.