SOLVED

Conditional Formatting on multiple rows

Copper Contributor

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:

JenSmith_0-1690886514397.png

 

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? 

 

Thanks in advance for your help 

J

 

 

 

2 Replies
best response confirmed by JenSmith (Copper Contributor)
Solution
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.
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)
1 best response

Accepted Solutions
best response confirmed by JenSmith (Copper Contributor)
Solution
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.

View solution in original post