Is it possible to use IF function inside the Conditional Formatting formula?

[Sh8dyDan]

Hello,

I am working to create a 2-week timesheet for personal use to track hours and calculate pay to later compare to the actual pay stub. We have a non-rotating day shift and a rotating 12-hour shift. We have a list of observed holidays I'd like the row that a holiday falls on to have a colored background. The kicker is that non-rotating and rotating shift holidays can at times be observed on different days. I created two defined name lists for each shift with the dates of their holidays, HolidayNonRotation and HolidayRotating. I'd like to use a set of Radio Buttons "Non-Rotating Shift" and "Rotating Shift" to switch between formatting. I can not find a solution similar to my use or create one of my own that works.

I've tried the code below with no luck. When the formula is TRUE it always returns FALSE. Even when I change the state of the Radio Button. I have verified referenced cells are correct.

=IF(AB6=1,MATCH($Z6,HolidaysNonRotating,0),MATCH($Z6,HolidaysRotating,0)

Thank you in advance,
Dan N.

 

[Sh8dyDan]

Check this and revert - I think formula needs slight modification

=OR(And($AB$6=1,MATCH($Z6,HolidaysNonRotating,0)),And($AB$6<>1,MATCH($Z6,HolidaysRotating,0)))

I don't understand why it never changes states with a radio button click.

No way to attach a file?

 

[SanjayGMusafir]

I don't understand why it never changes states with a radio button click.

You can do what I did - The formula is 2 parts

Paste 2 parts in 2 different cells and test if it is giving True or False when Radio Button is click

=And($AB$6=1,MATCH($Z6,HolidaysNonRotating,0))

=And($AB$6<>1,MATCH($Z6,HolidaysRotating,0))

Then we shall think of some alternative, it that does not work

Test both separately to identify the problem area...

 

[Sh8dyDan]

You can do what I did - The formula is 2 parts

Paste 2 parts in 2 different cells and test if it is giving True or False when Radio Button is click

=And($AB$6=1,MATCH($Z6,HolidaysNonRotating,0))

=And($AB$6<>1,MATCH($Z6,HolidaysRotating,0))

Then we shall think of some alternative, it that does not work

Test both separately to identify the problem area...

Ok, so yeah, individually they work. Highlight changes to on and off with the radio button change.

 

[SanjayGMusafir]

Ok, so yeah, individually they work. Highlight changes to on and off with the radio button change.

So you can use 2 rules for the same range and probably that shall serve your purpose.

 

[Sh8dyDan]

So you can use 2 rules for the same range and probably that shall serve your purpose.

Yep, that works. Like you said, we just needed to get away from the IF statement.

Thank you so much.
Dan N.

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.