color coding shapes

Hi all,

Hoping someone can help. I am trying to do some nutritional scoring, but ive created a small sample in the attached workbook.

I have the code that says if a number is smaller in column L than M then turn it red, if its larger, turn it green. There are some examples where there are no values. but as this is updated regularly these could have values at some point. Is there anyway to make this macro do the same thing it does now, but if there is no value like in rows 8 & 11 then the corresponding shapes will turn gray?

Thanks

Hi martin,

I have attached the code with the addition you made to gray out any cells that are 0/blank. but it's not working for me. It was ok when I had it just looking at 1 row (1 piece of code) but it grayed out a random shape.

Do you have any suggestions as to what I have done wrong?

Thanks so much for your help!

would have helped it I had attached the file in the 1st place

Hey Martin,

Thanks for your response, I certainly see the value of trimming the code down as I have 200+ items in my actual file as opposed to the 10 I included in my file

I guess your response only pasted 1 line of the code which shows turning one "shape" red. I would appreciate if you could re-paste the full code you had written which show Red, Green, & Gray depending on what is selected.

Thanks and have a great day! I still find VBA very comlex, but it's gradually making more sense the more i use it, very addictive! the more I learn and understand the power of it, the more i want to incorporate it into files.


If you have any recommendations on books they would be welcome!

Just wanted to put this back out there. I have played around with varying codes I have found online, but none that I am able to make work to show number larger than, smaller than, or no value. again very appreciative of anyone that can help, and very willing to learn as much as I can from you all!