Applying conditional formatting to a whole row based on just one cell

Hi all.

I have me a spreadsheet with 4 columns. Artist, Winamp, iPod, Status. What this spreadsheet is for is to allow me to check my Winamp library v my iPod to see if the iPod has the correct number of songs. It also helps me to see if I have any duplicate files pushing the total number of songs up.

So what I want to do is to apply conditional formatting to a whole row based on the Status cell. If the number I have for an artist is not equal to that same artist on my iPod the Status cell returns, "Error" in bold with a red background.

What I would like to do is to have the whole row for that artist go red with bold writing if the Status cell returns "Error". Obviously I know how to make the Status cell for each row go red, but how to extend that formatting to the rest of the row? As far as I can see I can only make a cell format itself conditionally based on itself, I cannot work out how to make other cells format themselves conditionally based on other cells.

Any help or tips greatly received.

In the Conditional format editing box, change 'Cell Value IS' to 'Formula Is'
In the formula box enter : =IF('status'="Error",TRUE)
'status' referes to your status cell. As you type the formula you will be able to move the select box on the sheet to the cell that you require. It will return a absolute reference eg $D$4, you will need to change this relative reference eg D4.
Set the format as you wish
You will then need to re-enter the conditional format for the other two cells in the row and finally copy the format to all the required cells in the sheet.

Hi Tomsk. Thanks for replying. I gave that a quick go but Excel 2007 is ruddy hard to do this with. It tries to do everything for you. I'll have another go later.

No need to do the re-entry of conditions, just select the cells that you want to apply the formatting to, then select conditional formatting and ensure that you pick out just the one cell and keep it as an absolute eg $D4 as above.

Saves a few keystrokes

Cheers Sadgeek. So do I have to do this for each row? Or can I select all the rows in the columns I want and make them look to the end cell in each row?

You can select the whole range. Just make sure you start at top left and go to bottom right, otherwise the formula needs to start with the final cell, not the first cell in Status.

Thanks. I've nearly got it. I selected the three columns and after a bit of fiddling got the formula to work. Weirdly it only works on the first column even though I selected all three.

Edit - It works now, for some reason I can only get this to work on one column at a time. No worries though, it works lovely now. Thanks all.

Make sure the formula is taking the correct column for Status ie has a $ before column reference. This ensures it always takes that column's reference, but changes the row number.

otherwise the column reference will move along, in a similar manner to the row as you go down.

Not sure I've explained that very well, soz

Yeah I know about $ in Excel.