Help: Simplify this formula

cbryan15

New Member
Joined
Mar 5, 2013
Messages
24
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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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?
 
Upvote 0

cbryan15

New Member
Joined
Mar 5, 2013
Messages
24
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!
 
Upvote 0

novel

New Member
Joined
Jan 16, 2012
Messages
39
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

Code:
=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.

wlk7m.png


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

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,195,617
Messages
6,010,728
Members
441,565
Latest member
menangterus556

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top