Highlight cells with Conditional Formatting using large range of values

DavidCB

New Member
Joined
Apr 7, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hello all, this is my first post.

I’m having an issue with Conditional Formatting. I work in a warehouse, and I am having issues in a certain section of the warehouse with items being split across several different stock locations rather than being consolidated into 1 or 2 locations.

There is an entire column of values that I am trying to format based off a giant range of stock locations that I have listed on my spreadsheet. If any cells in Column E match something in my giant range of stock locations listed in Column I (630 cells to be referenced in total, I2:I631), then the cells in Column E need to be highlighted.

Every time I try to come up with something in Conditional Formatting though, I keep getting error codes, or my methods don’t work.

I’d use filters, but some there is the stark possibility that some items referenced might have duplicated locations in other parts of the warehouse not referenced in Column I.
 

Attachments

  • Top of Spreadsheet.jpg
    Top of Spreadsheet.jpg
    248.1 KB · Views: 38
  • Bottom of Spreadsheet.jpg
    Bottom of Spreadsheet.jpg
    180.5 KB · Views: 40
  • Error Code example.jpg
    Error Code example.jpg
    190.3 KB · Views: 40

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi and welcome to MrExcel

- Select cells from E2 to the last cell with data, for example E200.
- Enter conditional format, formula and put the following formula.

=COUNTIF($I:$I,E2)

- Choose the color in format.
 
Upvote 0
Hmm... I tried it out, and for whatever reason, some criteria is being highlighted that falls out of the desired values, while some criteria that falls within the desired range is not. Stock locations inside the criteria of G/(even number) to K/28 should be highlighted, ones outside of that like E/ and M/ shouldn't.


Inside of criteria.jpg

Falling outside criteria.jpg
 
Upvote 0
You probably did not have the correct active cell when you applied the CF
Select cells from E2 to the last cell with data,
I would also suggest a change of CF formula as I think MATCH will be less resource-hungry compared to counting such a big range.
If you want to try this, remove any existing CF from column E then select from E2 down as far as you want then apply this CF formula.

20 04 07.xlsm
EFGHI
1
2C/18/08A/44/44
3Z67/2C/05/12
4C/05/12B/22/15
5B/22/16
6C/18/08
7
CF
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E2:E7Expression=MATCH(E2,I:I,0)textNO
 
Upvote 0
Success! This will save me so much time in the long run!

Thank you so much!
 
Upvote 0
P.S. Both formulas work fine on my PC. I'll probably stick with the MATCH function since the computers at work are pretty slow
 
Upvote 0
Glad you got them both going in the end. Thanks for the follow-up. :)
 
Upvote 0
One last thing, as sort of a forum etiquette thing, is there any way to mark the thread as solved? Saw something in the FAQ's about how you couldn't edit posts, but I'd figured I'd ask anyways
 
Upvote 0
is there any way to mark the thread as solved?
No formal way. Just a post saying you are satisfied (like you have) is all that is required, We deliberately don't mark as 'solved' because it is not uncommon for somebody to come along later with an even better solution and we do not want to discourage that. :)
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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
Back
Top