Highlight cells with Conditional Formatting using large range of values

[DavidCB]

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.

 

[DanteAmor]

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]

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.

 

[Peter_SSs]

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
	E	F	G	H	I
1
2	C/18/08				A/44/44
3	Z67/2				C/05/12
4	C/05/12				B/22/15
5					B/22/16
6					C/18/08
7
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E7	Expression	=MATCH(E2,I:I,0)	text	NO

 

[DavidCB]

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

Thank you so much!

 

[DavidCB]

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]

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

 

[DavidCB]

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]

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.

 

[DanteAmor]

P.S. Both formulas work fine on my PC

Im glad to help you. Thanks for the feedback.