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: 4
  • Bottom of Spreadsheet.jpg
    Bottom of Spreadsheet.jpg
    180.5 KB · Views: 4
  • Error Code example.jpg
    Error Code example.jpg
    190.3 KB · Views: 4

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.
 

DavidCB

New Member
Joined
Apr 7, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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
 

DavidCB

New Member
Joined
Apr 7, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

Success! This will save me so much time in the long run!

Thank you so much!
 

DavidCB

New Member
Joined
Apr 7, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad you got them both going in the end. Thanks for the follow-up. :)
 

DavidCB

New Member
Joined
Apr 7, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,701
Messages
5,524,405
Members
409,575
Latest member
navarrov74

This Week's Hot Topics

Top