If my Product table has a CategoryId column, I understand it can be a good practice to make the CategoryId the clustered index rather than the primary key ProductId.
Does this still apply if most of my queries against the Product table with look like select * from Product where CategoryId in (1, 2) instead of the more typical select * from Product where CategoryId = 1
Please very carefully pick your clustered index! It's very special - there can only ever be a single one per table, and it determines the physical ordering of the data, and it's used to uniquely identify the location of your data pages (the "row pointer", if you will).
Also, it's the most replicated data structure in your SQL Server database (assuming it's SQL Server you're talking about). The clustering key will be part of each and every non-clustered index on your table, too - certainly in the leaf level, possibly also in the index navigation structure.
You should use extreme care when picking a clustering key - it should be:
narrow (4 bytes ideal)
unique (it's the "row pointer" after all - if you don't make it unique, SQL Server will - for you - in the background - costs your a couple of bytes for each entry - times the number of rows and the number of nonclustered indices you have - can be very costly!)
static (never change - if possible)
ideally ever-increasing so you won't end up with horrible index fragmentation (a GUID is the total opposite of a good clustering key - for that particular reason)
it should be non-nullable and ideally also fixed with - a varchar(250) makes a very poor clustering key
Anything else should really be second and third level of importance behind these points ....
See some of Kimberly Tripp's (The Queen of Indexing) blog posts on the topic - anything she has written in her blog is absolutely invaluable - read it, digest it - live by it!
In your specific case, picking the CategoryId on a Products table doesn't sound like a very good idea. The category of a product might change, it's most likely not unique, and thus I think it wouldn't really make such a good clustering key.
Also, the category for a product doesn't sound like it'll be very selective, either - so it might not even make a good nonclustered index. If a particular query returns more than 1-5% of the total rows, most indices will not be used by the SQL Query optimizer anyway (since they return too much data).