Conditional format custom formula - IF(AND from range

[hro5e]

I have a range of dates:

K9 = 01/01/2021
K10 = 02/01/2021
K11 = 03/01/2021
K12 = 04/01/2021
K13 = 05/01/2021

I have 5 conditional formatting rules:

=IF($B19="PRE",AND(K$9>=$H19,K$9<=$I19))
=IF($B19="PRE",AND(K$10>=$H19,K$10<=$I19))
=IF($B19="PRE",AND(K$11>=$H19,K$11<=$I19))
=IF($B19="PRE",AND(K$12>=$H19,K$12<=$I19))
=IF($B19="PRE",AND(K$13>=$H19,K$13<=$I19))

How do I combine the above conditional formatting rules from 5 to 1, what is the formula?

 

[Joe4]

Do you want them all formatted exactly the same?
Or are you applying different formatting (i.e. different colors) to each one?

If you are applying different formatting to each one, then they would need separate CF rules (unless you are doing some sort of gradient shading).
That is just the way you do it.

If you want it to be formatted if ANY one of those conditions is met, just combine it into one long formula putting OR between each one.
If you want it to be formatted ONLY if ALL of those conditions is met, just combine it into one long formula putting AND between each one.

 

[hro5e]

Hi, they all have the same formatting, currently it is in 5 separate conditions (for the same format) but I have 20 different formats that require 5 formulas each, so it would mean I have 100 conditions, whereas if it could be 20, that would be amazing.

 

[Joe4]

Did you see what I wrote at the end of my previous post?

If you want it to be formatted if ANY one of those conditions is met, just combine it into one long formula putting OR between each one.
If you want it to be formatted ONLY if ALL of those conditions is met, just combine it into one long formula putting AND between each one.

Also, when writing conditional formatting rules, it is usually unnecessary to use IF, as we just want the formula to evaulate to TRUE or FALSE.

So, this:

=IF($B19="PRE",AND(K$9>=$H19,K$9<=$I19))

should be able to just be written like this:

=AND($B19="PRE",K$9>=$H19,K$9<=$I19)

Assuming that you want your value formatted if any one of those five formulas evaluate to true, I think you could just use:

=AND($B19="PRE",OR(AND(K$9>=$H19,K$9<=$I19),AND(K$10>=$H19,K$10<=$I19),AND(K$11>=$H19,K$11<=$I19),AND(K$12>=$H19,K$12<=$I19),AND(K$13>=$H19,K$13<=$I19)))

 

[hro5e]

Yes I did,

I tried this and seems to work

=IF($B19="PRE",OR(AND(K$9>=$H19,K$9<=$I19),OR(AND(K$10>=$H19,K$10<=$I19),OR(AND(K$11>=$H19,K$11<=$I19),OR(AND(K$12>=$H19,K$12<=$I19),OR(AND(K$13>=$H19,K$13<=$I19)))))))

But I also tried your last formula and works as well. I prefer your option because its shorter

 

[Joe4]

Yes I did,

I tried this and seems to work

=IF($B19="PRE",OR(AND(K$9>=$H19,K$9<=$I19),OR(AND(K$10>=$H19,K$10<=$I19),OR(AND(K$11>=$H19,K$11<=$I19),OR(AND(K$12>=$H19,K$12<=$I19),OR(AND(K$13>=$H19,K$13<=$I19)))))))

But I also tried your last formula and works as well. I prefer your option because its shorter

Yes, you do not need to nest and use OR(AND(... each time like that.

 

[pjmorris]

why not just highlight all 5 cells: click on K9 and drag to K13 then enter:

=AND($B$19="PRE",K9>=$H$19,K9<=$I$19)

with the appropriate formatting. Its important to click on K9 first as the application of the formatting will be confused were you to say click on K13 and drag up to K9.

Hope I've understood the question.

Regards