David Overton's Blog and Discussion Site
This site is my way to share my views and general business and IT information with you about Microsoft, IT solutions for ISVs, technologists and businesses, large and small.  

Browse by Tags

  • How to Use Office Conditional Formatting to put in icon sets comparing a range of cells, or relative references as Office calls it

    So it has been a while since I last dropped out a blog, but this one was useful to me at work, so I thought I would share. Office 2016 (or 2013 and 365) has some lovely functions, one of them being conditional formatting. I use this quite a lot, but one thing stumped me. Below is what I wanted to get to: When I wanted to compare to tables of data and put in place icons to show if the data had moved up or down, I could not do it. Excel would politely tell me that “This type of reference cannot be used in a Conditional Formatting formula” Now, not being one to like these limitations, I realised that I could do it cell by cell, but that would be an awful lots of mouse clicks, so I’ve written this short piece of VBA to do it for you. To open the VBA environment, press-Alt-F11 then right click your VBAProject and select Insert Module. You can then paste the code from below into that window and modify the CompareIcons sub to match your needs. The “CompareIcons” sub makes a call GenericIconsComparison...

(c)David Overton 2006-18