# Help: Simplify this formula

#### cbryan15

##### New Member
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
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

##### New Member
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?

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

#### novel

##### New Member
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.

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

#### Weazel

##### Well-known Member
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:

#### cbryan15

##### New Member
thanks guys! that solved this problem.

Replies
0
Views
153
Replies
8
Views
134
Replies
5
Views
297
Replies
1
Views
265
Replies
1
Views
301

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.

### Which adblocker are you using?

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

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