• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 88
  • Last Modified:

Heatmap Graphic Enhanced and Extended

I have a great little heatmap that Phillip Burton has helped me build.  I have one issue.  The Opal Shape is not properly responding to the sum data in S44 to reflect a color based on a average sum, numeric value with two decimal points.   It should be an easy fix for someone with Macro skills.  I simply need the ability for the right color to show up based on;

0-1.1 = Red
1.1-2=Yellow
2.1-3=Green

That's it!

B.
D--Data-Data-Temp-Cycle-Heatmap-Extended
0
Bright01
Asked:
Bright01
  • 2
  • 2
2 Solutions
 
Ejgil HedegaardCommented:
Try this.
I named cell S44 to CenterAverage, and used the name in the code to set the colour for Oval1.
Used If..Then..Else instead of Select Case to cover all values.
The code for Oval1 has to run for all changes in the range S32: S37.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 19 And (Target.Row > 31 And Target.Row < 38) Then
    
    With ActiveSheet.Shapes.Range(Array("Arrow" & (Target.Row) - 31)).Fill.ForeColor
        Select Case Target
        Case 1
          .RGB = RGB(0, 255, 0)
        Case 2
          .RGB = RGB(255, 255, 0)
        Case 3
            .RGB = RGB(255, 0, 0)
        End Select
    End With
    
    With ActiveSheet.Shapes.Range(Array("Oval1")).Fill.ForeColor
        If [CenterAverage] <= 1.1 Then
            .RGB = RGB(255, 0, 0)
        ElseIf [CenterAverage] <= 2 Then
            .RGB = RGB(255, 255, 0)
        Else
            .RGB = RGB(0, 255, 0)
        End If
    End With
End If
End Sub

Open in new window

Heatmap-Extended.xlsm
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Change CASE 1 to CASE 0 TO 1.1
change CASE 2 to CASE 1.1 TO 2
Change CASE 3 to CASE 2 TO 3
0
 
Bright01Author Commented:
Ejgil,

The code works!

Phillip,

Can you modify the example?  I tried the change but couldn't get it to work.

Thanks guys,

B.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 19 And (Target.Row > 31 And Target.Row < 38) Then
   
    With ActiveSheet.Shapes.Range(Array("Arrow" & (Target.Row) - 31)).Fill.ForeColor
        Select Case Target
        Case 1
          .RGB = RGB(0, 255, 0)
        Case 2
          .RGB = RGB(255, 255, 0)
        Case 3
            .RGB = RGB(255, 0, 0)
        End Select
    End With
   
    With ActiveSheet.Shapes.Range(Array("Oval1")).Fill.ForeColor
        Select Case [CenterAverage]
        CASE 0 TO 1.1
            .RGB = RGB(255, 0, 0)
        CASE 1.1 TO 2
            .RGB = RGB(255, 255, 0)
        CASE 2 TO 3
            .RGB = RGB(0, 255, 0)
        End Select
    End With
End If
End Sub
0
 
Bright01Author Commented:
Great job guys.  Both approaches worked and I learned a few new things....thank you for that.  In the end, I went with Phillip's solution because it is simple.... but both were equally effective.

Thanks again,

B.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now