How to use OFFSET Function with Conditional Formatting

[Marcie Be]

What is the best way to use the OFFSET Function with CF

I have a number of CF Formula that only apply to alternate rows. I have applied the formula long hand on every other row over the first 40 rows of my document. I now need to expand to cover 100+ rows. This manual method is not efficicient.

I do have a few OFFSET formula example =OFFSET($C5,-MOD(ROW(S4),2),0)="Record Change (RC)"

How would I apply the OFFSET Function to the following CF =NOT(ISBLANK($S5))

Other CF Formual that I need to OFFSET include

Cell Value =0

=IF($AO5="","",IF($AP5="","",IF(DAYS($AO5,$AP5),IF($AO6<=1,"True","False"))))

Formulae to be OFFSET are applied from Row 5 downwards

Thak you in advance

Best Regards

Marcie Be

 

[Joe4]

I have a number of CF Formula that only apply to alternate rows. I have applied the formula long hand on every other row over the first 40 rows of my document. I now need to expand to cover 100+ rows. This manual method is not efficicient.

Just make use of the ROW() function, which returns the row number of the row you place the formula in, and the ISODD or ISEVEN functions.

For example, if you only wanted to apply it to odd number rows, you could structure your CF formula like this:
=AND(ISODD(ROW()),your other conditions here)

 

[Marcie Be]

Just make use of the ROW() function, which returns the row number of the row you place the formula in, and the ISODD or ISEVEN functions.

For example, if you only wanted to apply it to odd number rows, you could structure your CF formula like this:
=AND(ISODD(ROW()),your other conditions here)

Thank you @Joe4 that has worked for me.
I have encountered an issue with the following

=AND(ISEVEN(ROW()),$S6>=$U6) Format Green
=AND(ISEVEN(ROW()),$S6<$U6) Format Red

Both starting from Cell S6 across the range =$S$5:$S$16,$S$19:$S$118

Everthing appears to be fine up to Rows 6 & 8, there after the formatting seems to be working in reverse

At Row 16 when Cell S16 contains the date 01/09/2020 and Cell U16 contains the date 02/09/20 S16 is Green when it should be Red

I am at a loss as to why this is happening

Regards

Marcie Be

 

[Joe4]

Your example works for me.

This part of your post is a bit confusing?

Both starting from Cell S6 across the range =$S$5:$S$16,$S$19:$S$118

Exactly which range are you applying this to?
Why not just apply it to S6:S19?
If looks like maybe you are trying to skipping cell S17, but there is no need to do this. Since 17 is an odd number, none of your conditions meet it.
Since it will be ignored anyway, there is no reason to break up the range like that.

 

[Marcie Be]

Range is S5 to S118

 

[Joe4]

Range is S5 to S118

When applying Conditional Formatting to a multi-cell range at once, you need to write the formula as it pertains to the very first cell in that range.
So if you are applying to S15 to S18, then your formula should be referencing S5 and U5, not S6 and U6.

 

[Marcie Be]

I had two rows in the file 17 & 18. These two rows seperated my Examples from the body of the tracker. I also used them for auditing purposes to indicate when I had checked the formula for each column above row 17 and then again below row 18.
I have removed the gap in the range and now cell S18 is red.
Can I send you an XL2BB of my file, as I still can not get it to work

 

[Joe4]

Can I send you an XL2BB of my file, as I still can not get it to work

You don't "send" an XL2BB image of the file, you just post it to this thread.

Things can a little weird if you start moving your ranges around, or try to apply CF to multiple disjointed ranges at once.
I would recommend removing all your Conditional Formatting and try applying those CF rules again.

 

[Marcie Be]

You don't "send" an XL2BB image of the file, you just post it to this thread.

Things can a little weird if you start moving your ranges around, or try to apply CF to multiple disjointed ranges at once.
I would recommend removing all your Conditional Formatting and try applying those CF rules again.

That is a big task, I have so many. I will give it a go.
Thank you for your help
Regards
Marcie Be

 

[Joe4]

That is a big task, I have so many. I will give it a go.

That could be part of the problem. If you have too many, they can start interfering with each other.
You may not necessarily need to eliminate all of the CF on the sheet. Maybe just the the ones from the range you want to apply this to.
So if that is easier, maybe start there before doing the whole sheet. Then, if that doesn't work, you can try the whole sheet.