Conditional Format

[Grawill]

I've not had much luck searching for this but it seems like there should be a really simple answer.

I have data in range A1:D600 and I'm attempting to highlight the row based off the data in one cell of that row.

Example:
Acc#, John, Doe, 123 Street, 5551212, NO JOB

If F="NO JOB" then I want all of this row to highlight red.

I'm able to do this if I use conditional formating for each row, however I do not want to have 600 conditional formats. I'm guessing there is an easier way, but I've failed as locating it as of yet.

If it effects anything.. the "NO JOB" line is obtained using this code:
=IF(J10=" ","NO JOB",(VLOOKUP(("*"&(J10)&"*"),I:I,1,FALSE)))

Thanks.

 

[T. Valko]

I've not had much luck searching for this but it seems like there should be a really simple answer.

I have data in range A1:D600 and I'm attempting to highlight the row based off the data in one cell of that row.

Example:
Acc#, John, Doe, 123 Street, 5551212, NO JOB

If F="NO JOB" then I want all of this row to highlight red.

I'm able to do this if I use conditional formating for each row, however I do not want to have 600 conditional formats. I'm guessing there is an easier way, but I've failed as locating it as of yet.

If it effects anything.. the "NO JOB" line is obtained using this code:
=IF(J10=" ","NO JOB",(VLOOKUP(("*"&(J10)&"*"),I:I,1,FALSE)))

Thanks.

You can apply the conditional formatting all at once.

What version of Excel are you using?

 

[Grawill]

Sorry.. should have included that first..
2010.

I've attempted using this:
="IF(F:F=""NO JOB"")"

But I see no change to the color of data/background.

Thanks for the quick response.

Ted.

 

[T. Valko]

Sorry.. should have included that first..
2010.

I've attempted using this:
="IF(F:F=""NO JOB"")"

But I see no change to the color of data/background.

Thanks for the quick response.

Ted.

Try this...

Let's assume the full range to format is A2:F10.

Select the *entire* range A2:F10 starting from cell A2.
Cell A2 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format

Enter this formula in the box below:

=$F2="No Job"

Click the Format button
Select the desired style(s)
OK out

 

[Grawill]

Thank you my friend. The format worked perfectly.. I just had to use this..

 =$F1="No Job"

When I used

=$F2="No Job"

as you posted, it worked, but highlighted one line up, instead of the same row as the "No Job".

Thanks again.

Ted.

 

[T. Valko]

Thank you my friend. The format worked perfectly.. I just had to use this..

=$F1="No Job"

When I used

=$F2="No Job"

as you posted, it worked, but highlighted one line up, instead of the same row as the "No Job".

Thanks again.

Ted.

Good deal. Thanks for the feedback!