Excel Conditional Formattign to Highlight Rows based on product

Hello,

i need here, what formula should i use in condtional formatting that for every unique product IDs a different color is applied.

for example the table below is a sample of a big data, i have more than 38 unique product IDs and each of them have multiple information, so now i want the columns A to D have a different color for each of the unique ID
for example, all rows with Apple product in it, should be red, then all rows with Mango, should have a different color, and so on the other product,  the color does not matter what it should be, as long as it is different color from other products.

thanks.


Product      Sale      Unit g      Time
Apple      32      0.690796324      11:02:41 PM
Apple      32      0.811899831      7:10:37 PM
Apple      50      0.305000433      8:05:08 PM
Apple      2      0.859959988      7:09:10 AM
Apple      68      0.057293474      11:12:22 AM
Mango      15      0.817944042      10:44:00 AM
Mango      35      0.270569891      3:09:30 AM
Mango      52      0.668334281      3:50:55 AM
Mango      19      0.44165958      9:28:24 PM
Mango      45      0.797796571      3:59:14 AM
Mango      6      0.109264038      2:06:04 PM
Mango      92      0.922820939      2:14:53 AM
Banana      65      0.558099824      5:41:15 PM
Banana      5      0.588986268      5:34:47 PM
Banana      10      0.335050458      1:06:47 AM
Banana      75      0.528190044      9:53:05 PM
Banana      32      0.418704919      12:00:29 AM
Banana      82      0.180723069      6:33:30 AM
Cherry      16      0.311014921      2:31:11 PM
Cherry      61      0.34833418      4:43:57 AM
Cherry      92      0.431833387      11:31:47 AM
Cherry      56      0.59902096      2:18:54 PM
Cherry      80      0.118215052      2:47:41 PM
Cherry      72      0.959456081      3:35:56 PM
Cherry      1      0.499259713      10:30:42 PM
LVL 6
FloraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
For each product to have a different colour you would have to have a separate rule for each product and have all rules applied to the range.

Will just an alternating colour suffice? This will change between two colours as the product changes.

Thanks
Rob H
0
EirmanChief Operations ManagerCommented:
Once you go above something like 15 colours on a monitor, it will become harder and harder
to find colours that stand out and that don't appear to merge into adjacent rows with similar colours.
There are only so many shades of green.

If you use a back/fore colour combination you could get up to perhaps 50.
0
Glenn RayExcel VBA DeveloperCommented:
I agree with the above comments, especially in regards to the number of colors applied.  With at least 38 different products, the color selection will get rather subtle and difficult to manage.

I wrote an EE article about setting up conditional "greenbar" effects in a worksheet to visually separate values in a manner like Rob described.  See if that might help resolve your issue.

Excel Conditional Formatting - The Better To See You With
(See the section titled "Ch..ch..ch..ch..Changes!")

Regards,
-Glenn
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

FloraAuthor Commented:
Thanks for your comments.

then how would I get only two color, like shading between each different product ID.  

for example Apple green and then mango yellow and then banana green again and so on
0
Rob HensonFinance AnalystCommented:
I do this on one of my reports at work. I have column A with a formula checking column for change in value, exact formula will be posted tomorrow.  My formula puts 1 or 0 then conditional formatting based on that.
0
Glenn RayExcel VBA DeveloperCommented:
Flora...see my article.  It describes exactly how to set up a helper column to create this kind of conditional formatting.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Formula starting in A3:

=IF(D3="","",IF(INDIRECT(LEFT(ADDRESS(ROW($D$2),COLUMN($D$2),4,1),1)&ROW())=INDIRECT(LEFT(ADDRESS(ROW($D$2),COLUMN($D$2),4,1),1)&ROW()-1),INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1)),ABS(INDIRECT(ADDRESS(ROW()-IF(INDIRECT(ADDRESS(ROW()-1,COLUMN(),4,1))="",2,1),COLUMN(),4,1))-1)))

Value to be checked for change is in column D but allows for it to move.

Allows for blank rows between groups and new rows to be inserted within the group without messing up the calc.

Conditional formatting then just looks at column A and formats colour if value = 1 otherwise no colour applied. Gives alternating coloured banding for each group change.
0
FloraAuthor Commented:
Sorry Rob,

I could not understand how to use your sophisticated formula.

my data already starts from column A and if I put your formula in A3 then what about my products which is already there?

Glenn.

thanks. I will look into the articles you have written.
0
FloraAuthor Commented:
Hi Rob,

I used your formula in the sample as my initial sample, but does not work. it does not give any result.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.