Shade alternating row where value in column "X" = 1

[excelishard987]

Hi, haven't been able to find an answer to this anywhere so here goes:

I want to shade alternate rows in a selection when a value in another column = 1.

So, for selection H11:T37, where the value in cell $D11 (through $D37) is 1, I want to shade alternate columns.

The reason for this is I have a filter on column D that hides rows there the value is not 1.

Any ideas? Thanks in advance.

 

[shg]

CF formula in A2 and down: =MOD(SUM($A$1:$A2), 2)

Before filtering:

	A​
1​	Test​
2​	1​
3​	0​
4​	0​
5​	1​
6​	0​
7​	0​
8​	1​
9​	1​
10​	1​
11​	1​
12​	1​
13​	1​
14​	1​
15​	0​
16​	1​
17​	0​
18​	1​

After:

	A​
1​	Test​
2​	1​
5​	1​
8​	1​
9​	1​
10​	1​
11​	1​
12​	1​
13​	1​
14​	1​
16​	1​
18​	1​

 

[excelishard987]

This isn't shading anything, but I've used a variation of this before to shade alternate rows. Problem is I need to shade cells in column H:T using the value in column D

I thought =AND($D11=1,MOD(ROW(),2)=0) would work but it doesn't, seems it's shading rows randomly

 

[shg]

CF formula in H2:T2 and copied down: =MOD(SUM($D$1:$D2), 2)

Before filter:

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

T​

1​

Test​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

2​

1​

3​

0​

4​

0​

5​

1​

6​

0​

7​

0​

8​

1​

9​

1​

10​

1​

11​

1​

12​

1​

13​

1​

14​

1​

15​

0​

16​

1​

17​

0​

18​

1​

After:

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

T​

1​

Test​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

2​

1​

5​

1​

8​

1​

9​

1​

10​

1​

11​

1​

12​

1​

13​

1​

14​

1​

16​

1​

18​

1​

 

[excelishard987]

Not working on my machine... Feel like it should be working though, f***!!!

Before filter: https://imgur.com/VJJfJmR

After filter: https://imgur.com/a/tWBybzR

 

[shg]

The $ signs are important ...

 

[excelishard987]

Well, yeah. Those are in my formula as well, still not working like your example.

Post-filter with formula shown: https://imgur.com/Zf9HRKz

 

[shg]

Dunno. If you put a workbook on box or dropbox and post a link, I'll look at it.

 

[excelishard987]

I figured out a workaround!

In column E I was using a filter to assign 1 to rows I would keep or 0 to rows I would hide. I added a helper column in D that summed up values from $e$2:$e3, so no $ before the 3 means the formula would continually assign a +1 for every new row where value in column E was 1.

Then the conditional formatting was just =iseven($d3) or =isodd($d3) dragged down throughout the range I wanted to fill.

Thanks for your help man, if you want I'll throw this in a dropbox when I have some free time in case you want to have it as reference.

 

[shg]

I'm good, thanks.