# Change cell colour based on value from a vlookup?

#### blemon

##### New Member
Hi all,
I am still very much a beginner with excel but I am trying to create a warehouse location tracking spreadsheet to help me manage my warehouse a bit better (which I know is over my head but I like the challenge).
I don't even know if I am going to properly ask this question but here goes.

I have a worksheet that has all the possible pallet locations in my warehouse (much more but you get the point):

I have another sheet that has each location on a row, with it's item and quantity, etc. (we will call this live inventory).
I created a pivot table from the live inventory, because sometimes the locations repeat if there are more than one product in that location. I only need to know there is something in there so I copied the data from the pivot table, leaving the value as "count".

My question is, how can I format the pallet position cells so they will show red if the pivot table shows something there?

Again I am a beginner, but I've tried:

conditional formatting, with a vlookup formula that works but it only works on one cell and if I drag the formula, it changes the whole thing to red.
an if formula with a nested vlookup that went nowhere
should I be using an hlookup? I don't think so, because the pivot table is vertical, am I wrong?
do I even need conditional formatting? Should this be a simple IF formula?

and I'm not sure exactly how to word this properly so I've been having a hard time googling stuff. Lots of conditional formatting videos, but I'm lost and/or overthinking it or completely looking the wrong way.

any guidance would be appreciated.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### jtakw

##### Well-known Member
Hi,

If I understand correctly.

Use this formula for CF (conditional formatting) in your Pallet Location sheet:

Excel Formula:
``=COUNTIFS(Inventory!\$A\$1:\$A\$1000,A1,Inventory!\$B\$1:\$B\$1000,">0")``

Where A1 is the 1st cell of your Pallet Location Table
Change Inventory! (sheet name) to match your Live Inventory sheet, change/adjust cell references/ranges as needed.

#### blemon

##### New Member
Hi,

If I understand correctly.

Use this formula for CF (conditional formatting) in your Pallet Location sheet:

Excel Formula:
``=COUNTIFS(Inventory!\$A\$1:\$A\$1000,A1,Inventory!\$B\$1:\$B\$1000,">0")``

Where A1 is the 1st cell of your Pallet Location Table
Change Inventory! (sheet name) to match your Live Inventory sheet, change/adjust cell references/ranges as needed.
Hi,
Thank you very much. i will try this.

Replies
3
Views
65
Replies
0
Views
15
Replies
3
Views
100
Replies
11
Views
296
Replies
6
Views
163

1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

### 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.

### Which adblocker are you using?

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

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