SOLVED

# Highlight series of cells if 1st or last row contains text

Copper Contributor

# Highlight series of cells if 1st or last row contains text

Hello, I have a worksheet that contains a few rows for each employee.  The ID # is in column A, and column E will contain a calculated numeric value.   For each employee, I would like to highlight all rows of Column A, if any rows in column E for that employee are > 0.  Note that the number of rows for each employee may vary.

I'm fine doing this with either conditional formatting or a formula.

So, in the attached:

# 111 has a value of 1 in the first row, so I would like to highlight all rows in column A for that person

# 666 has a value of 2 in the last row, so I would also like to highlight all rows for that person

IDs 222,444, and 555 have 0 in all rows, so nothing is highlighted for them

4 Replies

# Re: Highlight series of cells if 1st or last row contains text

IDs 222,444, and 555 don't have 0 in all rows, so nothing is highlighted for them

don't?
or
do?

# Re: Highlight series of cells if 1st or last row contains text

They DO have ..thanks for the catch, I edited the message.
best response confirmed by richzip505 (Copper Contributor)
Solution

# Re: Highlight series of cells if 1st or last row contains text

@richzip505 You may use the following formula-

``=IF(SUM(FILTER(\$E\$2:\$E\$32,\$A\$2:\$A\$32=A2))>0,TRUE,FALSE)``

Actually you don't need IF(). Just could try-

``=SUM(FILTER(\$E\$2:\$E\$32,\$A\$2:\$A\$32=A2))>0``

# Re: Highlight series of cells if 1st or last row contains text

@Harun24HR   Thank you, that works perfectly!

1 best response

Accepted Solutions
best response confirmed by richzip505 (Copper Contributor)
Solution

# Re: Highlight series of cells if 1st or last row contains text

@richzip505 You may use the following formula-

``=IF(SUM(FILTER(\$E\$2:\$E\$32,\$A\$2:\$A\$32=A2))>0,TRUE,FALSE)``

Actually you don't need IF(). Just could try-

``=SUM(FILTER(\$E\$2:\$E\$32,\$A\$2:\$A\$32=A2))>0``