Linking conditional formatting with shapes OR Bing Map method on a photo

[juliabrushett]

Hello,

I am trying to create a heat map of the facility I work in. Currently, I have conditional formatting set to darken cell color as each microbial instance is input into the worksheet at each location, but I would like to transfer this to points (like, literal circular spots) on a blueprint of the facility floor. Is there any way to do this? Some way to link the shape to the conditionally-formatted cells? Or can I use Bing Maps, but change the map background to my blueprint?


Thank you SO much in advance for any assistance!

 

[tonyyy]

juliabrushett,

Welcome to the Board.

Some way to link the shape to the conditionally-formatted cells?

One method to link a shape to a conditionally formatted cell is with a line of vba code, for example...

ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = Range("A1").DisplayFormat.Interior.Color

So a simplistic approach to create a heat map would be to...
Insert a pic of your blueprint onto a worksheet; right click it and Send To Back.
Insert shapes overlaying the blueprint.
Link the shapes to the conditionally formatted cells.

To have the shapes' color update when the conditionally formatted cells are updated, you might consider the following as an example...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:D23")) Is Nothing Then
    ActiveSheet.Shapes("Oval 1").Fill.ForeColor.RGB = Range("A1").DisplayFormat.Interior.Color
    ActiveSheet.Shapes("Oval 2").Fill.ForeColor.RGB = Range("B7").DisplayFormat.Interior.Color
    ActiveSheet.Shapes("Oval 3").Fill.ForeColor.RGB = Range("C15").DisplayFormat.Interior.Color
    ActiveSheet.Shapes("Oval 4").Fill.ForeColor.RGB = Range("D20").DisplayFormat.Interior.Color
End If
End Sub

The code should be pasted into the Sheet module that contains the conditionally formatted cells and blueprint.

nota bene: Extracting or matching the color from a Conditionally Formatted cell can be troublesome. The above works with a 2-Color Scale format.

Cheers,

tonyyy

 

[juliabrushett]

Thank you SO much for your reply!

I ended up finding a method to have a 3-color scale, which I am attempting to use, but I am not quite sure why my Macro is not showing up in when I try to run my code. Here are a couple of photos to maybe help troubleshoot the issue-- before and after I try to run the code:

 

[tonyyy]

Photos? What photos?

And the code is triggered by a Worksheet Change event; meaning, when a value is changed on the sheet in Range("A1:D23") - the code will run.

 

[juliabrushett]

Ahh, totally skipped over that part.

I was thinking, wouldn't it be possible for me to just retrieve and save the RGB code in a variable and then use that variable to spit out the shape color? How would i do this?