Forum Discussion
Conditional formatting - color bars based on values in other cells
Hi guys,
I have several cells containing text, and I want to highlight those cells by color from best to worst to make it easy for people to see which is better without needing intimate knowledge of the field. For example, if I was comparing the material a tool was made from and wanted to highlight the cells showing which material was better or worse than others:
| A | |
| 1 | Titanium |
| 2 | Plastic |
| 3 | Wood |
| 4 | Stainless Steel |
I can't just highlight column A and say Conditional Formatting > Color Bars, because Excel can't know which one is better or worse. What I imagine I would need is another column next to it containing numbers which correlate to best to worse, and the value of those cells is used to conditionally format my text cells, as follows:
| A | B | |
| 1 | Titanium | 1 |
| 2 | Plastic | 3 |
| 3 | Wood | 4 |
| 4 | Stainless Steel | 2 |
So the result I want would be as if I applied Conditional Formatting > Color Bars to column B, but have the colors applied to the text in Column A (i.e. Titanium would be Green, Wood would be Red, and the others would be shades in between).
How can I do this please? Bear in mind that it's possible that the data source I've put into Column B would not necessarily be in a neighboring column or not necessarily even in the same row.
Thanks!
6 Replies
- Maybe you can use the CountByFontColor function provided by kutools for excel
GEEK_21Thanks, I'm not familiar with that suite, but it sounds like that function will count how many cells are of a particular color. If that's what it does I don't think that will help me - I need to conditionally color cells based on a number, not count how many cells are already of a particular color. Could you please clarify how that function would help if I was to buy the tool suite?
