Conditional Format

Grawill

New Member
Joined
Nov 24, 2011
Messages
27
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Nov 24, 2011
Messages
27
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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Nov 24, 2011
Messages
27
Thank you my friend. The format worked perfectly.. I just had to use this..
Code:
 =$F1="No Job"

When I used
Code:
=$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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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! :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,127,588
Messages
5,625,668
Members
416,124
Latest member
DeMoNloK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top