Help with formula to reference other cell to highlight fill another

[msdclark]

In my spreadsheet I use a Data Validation list in column J.


I input item numbers in column A that use a VLOOKUP formula to pull a description of that item# from another spreadsheet and place it in Column B. Of course this happens row by row as the item numbers are manually entered in each cell of column A.
I need to create a formula that if I choose a specific choice from the data validation list in Column J, it will highlight fill the description that rests in the corresponding row cell of Column B. In this case that description is "Urgent Need."


I need this to include the entire column in each case as I'll keep adding and deleting lines based on a given week's input.

[FDibbins]

Hi and welcome to to the forum

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =J2="Urgent Need"..............................format fill RED

Edit: Thanks for catching that Trevor

[TMS]

@Ford: should step 4 have two equals signs (and no minus sign)?


Regards, TMS

[msdclark]

It isn't working. Perhaps I don't understand some of the steps.
1. Start off by highlighting column or selection where I want the final highlighting/cell fill to occur. In this case, Column B
2. Click on Conditional Formatting > New Rule >Use a formula to determine which cells to format
3. In the Format values where this formula is true: I type =J2="Urgent Need"
4. Click Format button > Choose red
5. Click Okay, Okay

Nothing happened in column B which I had selected at the beginning

I even changed your format to read =$J:$J="Urgent Need"
in case it didn't recognizing searching the column.

Could it be because it must find the exact J cell validation item per row?
So I input my information in cell A2 which uses a VLookup and inputs the corresponding information in B2, then I choose "Urgent Need" in J2 from the Data Validation List. Whatever formula I use should know to highlight B2 based on the fact that my choice was "Urgent Need" in J2 instead of just "Need."

I'm sorry if I'm confusing. I do learn every time someone provides me a solution so I ram arely on these boards until I have my next stumping formula.

[TMS]

You need to select cells in column B. Select cells B2 down to wherever. Note the formula MUST relate to the first row selected.

=AND($J2<>"",$J2="Urgent Need")

See the example workbook.

Regards, TMS