Conditional Format based on every other cell in a column

[handofthrawn]

Right now I have a list of numbers from A1:A8. I want B1 to have a conditional format that looks at A1:A8, skips every other cell (A2, A4, A6, and A8), and let me know if either A1, A3, A5, or A7 have a value of less than 1 (0 or negative). So its skipping every other row and only looking at one column. If that happens, B1 turns red.

I'm not good with the MOD function and am struggling with conditional formatting. In the conditional format, I have tried using the mod function so it would be like =MOD(A1:A8, 2) < 1
but I'm obviously messing something up. If anyone knows what I'm doing wrong I would greatly appreciate the help.

A1: 600
A2: -592
A3: -1
A4: -17
A5: 350
A6 -560
A7: 0
A8: 15

 

[handofthrawn]

Maybe it would be easier if I put an IF() in a cell to look for A1, A3, A5, and A8 to see if any are < 1. If so, return a 1 or a 2. Then conditional format that cell?

I'm trying to remember how I did this previously. Maybe I had MOD(ROW()) in there? Sorry for the double post as I'm trying to work through this.

 

[dreid1011]

You mentioned just highlighting B1, but I'm unsure what formula will do that, as I don't think you can put array formulas into conditional formatting. So, I have a formula that will highlight the corresponding cell in B1:B8 red.

As per your example, B3 is red using the following formula.

This is the formula I used:

=AND(MOD(ROW(A1),2)>0,A1<0)

Select B1:B8 > Home tab > conditional formatting > new rule > use formula

 

[Jonmo1]

Is there anything else in common between the rows you want included besides 'every other row' ?
Perhaps an adjescent column like B contains a specific text string?

So we could do something like
=COUNTIFS(A1:A8,"<=1",B1:B8,"Yes")

It would only consider the cell in A if the corresponding cell in B = Yes

 

[handofthrawn]

Thanks guys. I think you are right dreid1011 in that I can't use array formula in there.

Dreid1011, is there a way I could use an IF statement like this to check every other cell in the column and then return a 1 or -2, then conditional format that response?
This formula I put in B1 isn't working but I feel like I am close.

=IF(MOD(ROW(A1:A8), 2) < 1, 1, -2)

 

[dreid1011]

It's not going to work just sitting in B1 alone. You would have to copy it from B1 to B8 and change the ROW(A1:A8) to just ROW(A1) before you copy it down.