Conditional Formatting a column with Blank cells based on text in another column.

Question

The columns titled Spfld, Dover and Boston are item numbers.

The column titled Status indicates if the item is active.

I need all blank item number cells for items where the product status is "active" to be filled with a color.

Any item with a product status denied should have no color.

Product Name	Spfld	Moline	Chicago	Product Status
Test one	45897		85331	Active
Test two				Denied
Test three		59318		Active

Answer 1

You are welcome. Did that resolve the issue ?

Answer 2

Hello,

Can you get a screenshot of the spreadsheet, so I can see how the columns/data layout is like to be exact, then I will write you the formula you can use in the conditional format settings using correct referencing for cells and range.

Answer 3

This is how I want it to look.

Answer 4

Here is the procedure to apply the Conditional Formatting as you describe.

1. Select all of the cells that you want to be evaluated for the Conditional Formatting. In the example below that is B2:D4. It is important that the first cell selected matches the cell reference in the formula. If you start the selection anywhere other than B2 adjust the formula to match that and make sure the E2 Status column cell reference is the correct column and matches that same row.
2. On the Home ribbon open Conditional Formatting>New Rule>Use a Formula. In the Format Values line enter this formula adjusted for the locations of your first-row data as needed.

=AND(B2="",$E2<>"Denied")

3. Click on the Format button at the lower right and select the formatting you want in the Format Cells dialogue. Then click OK and OK.

Answer 5

Thank you for the screenshot. Super helpful.

I created this template for you, you can download from this OneDrive link and it is already set up for you.

https://1drv.ms/x/c/e7d90eb3e3571518/Edhh2BWIDo...