Conditional Formatting using advanced formulas

[FelipeVaz]

Hi folks, how are you doing!

I'm facing a challenge trying to find out and conditional formatting a vertical cell range against a horizontal cell range, both with dates. At least, that is what I think to be my challenge, !

In the link below a model and instructions:

OneDrive file link: http://1drv.ms/1M51pdH

Ps.: using formulas, not VBA.

Thanks

 

[barry houdini]

Try this formula for conditional formatting to colour all cells yellow

=COUNTIFS(DataInício,"<="&B7,DataFim,">="&B7)

If you want to distinguish yellow and blue it's probably easier to add a formula to checklist spreadsheet, e.g. in S2 copied down

=COUNTA(B2:R2)

Name that column "Counter"

Now for blue condition use

=COUNTIFS(DataInício,"<="&B7,DataFim,">="&B7,Counter,17)

You need to have blue condition appear in the list before the yellow condition

 

[FelipeVaz]

Try this formula for conditional formatting to colour all cells yellow

=COUNTIFS(DataInício,"<="&B7,DataFim,">="&B7)

If you want to distinguish yellow and blue it's probably easier to add a formula to checklist spreadsheet, e.g. in S2 copied down

=COUNTA(B2:R2)

Name that column "Counter"

Now for blue condition use

=COUNTIFS(DataInício,"<="&B7,DataFim,">="&B7,Counter,17)

You need to have blue condition appear in the list before the yellow condition

Works perfectly! Thanks Barry houdini.

I was thinking wrong doing in that way. gooosh!

Thanks