Conditional formatting using icon sets across whole sheet

[Coventry G]

Hello one and all. I am trying to create a preferred supplier based on lowest price. I have attached the test sheet. You will see that I have had some success using a dedicated "preferred" column to show me which is best. I am happy with this but then I found icon sets in conditional formatting and like the tick icon to also show who is the cheapest. Again you will see I have managed this but only on one row only.
I am unable to figure out why I cannot highlight a whole array to speed up this process.
I would like the tick to show in the lowest price cell for that given supplier and be able to do this "en mass" rather than the only way I can do it which is to highlight the three cells in that size row and run the format.

can anyone help me please. I would think its a basic enough command but my experience is not great. I am still learning with your help..

thanks in advance and will leave good Kudos to any contributors.

[Philb1]

Hello

I don't think there is a way of doing what you're after with worksheet conditional formatting so here is a VBA solution, probably not the final solution, but something to get you going in the right direction.
There's a textbox on sheet 1 explaining how it works.
Have a play & see how it goes
Cheers
Phil

I see this useless site still hasn't fixed the file uploading. There's a dropbox link below to download

https://www.dropbox.com/s/8skb05ycld...Test.xlsb?dl=0

Please Login or Register  to view this content.

[Coventry G]

Thank you for your contribution. I have still not entered the world of macros s have no idea how you have created this file. I can see in principle it works however my excel 2007 version will not show what your macro is doing, it returns into debug mode as it appears the object green tick will not work?

see my screenshot

[Coventry G]

Ive managed to solve this but not using the ticks. I have managed to use conditional formatting with my own formula in order to highlight the lowest price within the row.
I then used format painter over the sheet to replicate and it worked a treat.
so it now highlights all my lowest buying prices.

great as there are over 400 rows on the full sheet and then there is another 3 sheets with the same type of issue.

thanks for assistance.
I have posted my worksheet here in case anyone else has a similar issue so they can see "a" solution without the use of a macro

[Philb1]

Glad you found a solution that works for you.

If you want to you can record a macro using the icons you want to use, look at the recorded code and then change the green tick part in my code to whatever you recorded.