• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Random Font Color Change?

Gregg Wolin

Member
I'm struggling to conditionally format the color of an icon ( / ) based on the value of the neighboring cell. The screenshot illustrates my goal. The first column only has 2 of the same entries so a single color (/) is fine. Column 2 has 4 total entries, with 2 being unique. Column 3 illustrates what I'm going for with 4 unique entries. As there could be a large number of unique entries, using a color ramp based on the number of unique category entries would be ideal. Is this possible?
Screen Shot 2020-10-10 at 2.27.10 PM.png
 
In general, it's best to have lookup table for grouping. Then add helper column to facilitate conditional format etc.

Ex: Create Unique list of data. Add index number beside it. Then use Index/Match, VLOOKUP etc to bring over the group index.
Then create conditional format based on it.

See attached sample.
Up to 4 or 5 can be done using Icon set as in column A.

Anything more... you could use gradient fill using CF (Column C).
Or use multiple CF rules to set color for #1 to x (Column D)
But there's no CF approach that can automatically set dynamic number of format/color for range, other than using VBA.

Another approach is to use shapes mapped out to lookup table. Then use method outlined in link. But I'm not fan of this method. Since you'll need to set up place holder object for the entire range.
 

Attachments

  • Sample.xlsx
    11.1 KB · Views: 11
Back
Top