Format picture based on cell value

Hello all!

I'm trying to figure out how to use a picture to illustrate the % of college degree completions in Oregon. I would like to format a picture, say the level of transparency, depending on the % completion in another cell. So, for example, if I crop the picture in quarters, and the completion rate is only 50% in cell A1, I would like the bottom 2 quarters to be in full color, and the top 2 quarters to be 40% transparent.

Is this possible? And if so...can you help me with the code?

Thanks!

Welcome to the forum Betsy

Try the attached file

You must to add the pictures into the worksheet in a particular way to allow them to be made transparent - I will explain this later if the macro below is doing what you want. (There is a second macro in the workbook which will become relevant then)


There are 4 images on Sheet1 - which are manipulated
Change the value in cell A1 and then hit {CTRL} + t to run the macro
Try any percentage between 0 and 100
To make it more obvious I set the transparency level to 70%
You can set it to whatever you want, if you amend the value of t in this line:

Please Login or Register  to view this content.

The macro begins by setting all the images to the transparency level equal to the value of "t", then looks to value in cell A1 and to set transparency to 0 as determined by %complete

Please Login or Register  to view this content.

And you could simply use conditional formatting (no code required)
Some examples in attached workbook

Select a cell to which conditional formatting has been applied and
on HOME tab
Conditional Formatting
Manage Rules
choose a Rule
Edit Rule

ConditFormat101.jpg

THANK YOU KEV!! Both of these work perfectly. I would like to know more about adding the pics using your first example. Also I imagine it would be possible to create a macro button to run the Ctrl t, just to reduce the keystrokes?

Thank you SOOOO much!

Betsy

The macro below (not mine, I found it somewhere and adapted it) is already in the file attached to my first post (= post#02)
I created the 4 pictures (exactly the same size), then imported them one at a time into the worksheet using the macro.

It is helpful to give the pictures meaningful names (this is not the filename but the name recognised within Excel)
- I used names Boat 25%, Boat 50%, Boat 75%, Boat 100%

Run the macro with {CTRL} + s

- an input box pops up asking for a meaningful name (not the filename).
- overwrite ??? with anything you like but do not leave it blank
(I have not put any checks in to check for invalid characters - so use sensible normal letters and numbers - otherwise the VBA may crash later in the code)
- navigate to the appropriate folder and find the file with the first picture.
- the picture should appear in your sheet
- repeat for the other pictures
- move the pictures to where you want them

The 100,100 are x and y co-ordinates for left corner of picture
The 100,50 are the width and height of the picture

Please Login or Register  to view this content.

Good luck! Come back with any further questions
Kev

Please Login or Register  to view this content.

Thanks again Kev....this is exactly what I needed and I learned a lot too. So I can customize the next one. Thank you for being so helpful!

Betsy

You are welcome

Feel free to click on * to add reputation if you are happy with help given

Also if your original question is answered please click on Thread Tools (top of thread) and mark thread as solved (keeps things tidy)
But you can still ask further questions after that if you need some associated help.
Kev

Excellent! I will do both of those. Thanks for walking me through this...first time on forum! But...I'm sure I'll be back since I seem to say "I KNOW Excel SHOULD be able to do this"...like every day!

Thanks again, really appreciate you!