SOLVED

# Conditional Formatting on multiple rows

Copper Contributor

# Conditional Formatting on multiple rows

Hi All,

I have a spreadsheet where i need rows to auto 'grey-out' when that row is marked as closed in a cell on the row.

I have worked out the conditional formatting for a single row but can't seem to apply it to the rest of the spreadsheet unless i manually add the conditional format rule on each row - i am hoping there is a quicker way of doing this since it needs to be applied to the whole sheet of several thousand rows!

I have data in columns A-M for all rows, row G is the identifier of 'CLOSED' where i am basing my conditional formatting and need it to colour all cells within that row from columns A-K.

For single row i am using the formula (=\$G\$26="CLOSED") to determine if the row should grey out.

i.e. On row 3, if cell G3 contains the text 'CLOSED', cells A3-K3 colour grey. Simple example screenshot attached:

When i have tried to copy the conditional formatting over all rows, it seems to only link back to one specific cell (i.e. specifically G3) for all rows as opposed to the G cell on the relevant row.

Is there an easier way to do this?

J

2 Replies
best response confirmed by JenSmith (Copper Contributor)
Solution

# Re: Conditional Formatting on multiple rows

Take the \$ off of the 3 so the formula for the applied to range of A3:z1000 will all be based on the upper left cell (A3) so
=(\$G3="CLOSED")
which mean always use column G but move row 3 to be the same row as the cell being checked.

# Re: Conditional Formatting on multiple rows

Ah amazing, thank you! That's worked perfectly :)
(i've never truly got to grips with the purpose of the \$'s so this change hadn't occurred to me)