Highlight cells with Conditional Formatting using large range of values

DavidCB

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

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,567
Office Version
2007
Platform
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
365, 2019, 2016
Platform
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
45,918
Office Version
365
Platform
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
365, 2019, 2016
Platform
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
365, 2019, 2016
Platform
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
45,918
Office Version
365
Platform
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
365, 2019, 2016
Platform
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
45,918
Office Version
365
Platform
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. :)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,110
Messages
5,509,264
Members
408,720
Latest member
Edna123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top