Multiple worksheet_change with same macro with different reference cell

Red Eye

Want to create some dynamic dashboard in which the shapes will alter color as the reference cell's value would change (based on some threshold value).

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("M5")) Is Nothing Then Exit Sub
    If IsNumeric(Target.Value) Then
        If Target.Value < Range("$AA$5") Then
            ActiveSheet.Shapes("Isosceles Triangle 3").Fill.ForeColor.RGB = vbRed
        ElseIf Target.Value >= Range("$AA$5") And Target.Value < Range("$Y$5") Then
            ActiveSheet.Shapes("Isosceles Triangle 3").Fill.ForeColor.RGB = vbYellow
        ElseIf Target.Value >= Range("$Y$5") And Target.Value < Range("$Z$5") Then
            ActiveSheet.Shapes("Isosceles Triangle 3").Fill.ForeColor.RGB = vbGreen
        ElseIf Target.Value >= Range("$Z$5") And Target.Value < Range("$AB$5") Then
            ActiveSheet.Shapes("Isosceles Triangle 3").Fill.ForeColor.RGB = vbYellow
        Else
            ActiveSheet.Shapes("Isosceles Triangle 3").Fill.ForeColor.RGB = vbRed
        End If
    End If   

End Sub

This is working fine for one Triangle in the Dashboard with some defined threshold value (changing color dynamically). Want replicate the same operation with other triangles as well. How that can be done?

[![The sequence of Triangles][1]][1]

Edited & Added part:

The dashboard has multiple Brand wise performance to showcase.

The schematic diagram:[enter image description here][2]

The Actual data across brands at CFA, DB & SS level : Actual Data [enter image description here][3]

The threshold level across CFA, SS & Sub D: [enter image description here][4]

So for every brand (Brand 1,2 &3) at stocking points (CFA,DB & SS level), the color code behave like:

If the actual< UCL2, the triangle will be red (e.g. Brand1 at CFA is 9, respective tringle will be red), UCL2<= actual Yellow , UCL1<= actual Green , LCL1<= actual Yellow , Actual =>LCL2 --> Red

Hope now the problem is much more crystal. Anticipating assistance regarding this...TIA

AcsErno

EDITED on your request

and sligthly refactored

Private Sub Worksheet_Change(ByVal Target As Range)

     Colorize ActiveSheet.Shapes("Isosceles Triangle 1"), Target, Range, ("M1"), Range("$AA$5").Value, Range("$AB$5").Value, Range("$Y$5").Value, Range("$Z$5").Value
     Colorize ActiveSheet.Shapes("Isosceles Triangle 2"), Target, Range("M3"), 19, 60, 32, 38
     'Colorize ActiveSheet.Shapes("Isosceles Triangle 3"), Target, Range("M5")

End Sub

Private Sub Colorize(shp As Shape, ByVal Target As Range, rValue as Range, _
     YellowLow As long, YellowHigh As Long, _
     GreenLow As Long, GreenHigh As Long)

     Dim iColor As Long

     If Intersect(Target, rValue) Is Nothing Then Exit Sub

     'If IsNumeric(Target.Value) Then
     '    iColor = vbRed
     '    If Target.Value < Range("$AA$5") Then
     '        iColor = vbRed
     '    ElseIf Target.Value >= Range("$AA$5") And Target.Value < Range("$Y$5") Then
     '        iColor = vbYellow
     '    ElseIf Target.Value >= Range("$Y$5") And Target.Value < Range("$Z$5") Then
     '        iColor = vbGreen
     '    ElseIf Target.Value >= Range("$Z$5") And Target.Value < Range("$AB$5") Then
     '        iColor = vbYellow
     '    End If
     If IsNumeric(Target.Value) Then
         iColor = vbRed
         If Target.Value >= YellowLow And Target.Value <= YellowHigh Then  iColor = vbYellow
         If Target.Value >= GreenLow And Target.Value <= GreenHigh Then    iColor = vbGreen
         shp.Fill.ForeColor.RGB = iColor
    End If  
End Sub 

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Worksheet_Change - Targeting multiple cells simultaneously

From Dev

link Open/Libre Office button to cell and reference cell in macro

From Dev

How to change cell value using Worksheet_change event without triggering a second call

From Dev

Excel hyperlink follow macro after worksheet_change event

From Dev

Worksheet_change macro running multiple times in excel 2007

From Dev

MATLAB: How to create cell arrays with multiple values in correspondence of the same reference

From Dev

Combining multiple macros (worksheet_change)

From Dev

How to manually modify a cell that is being edited in a worksheet_change event?

From Dev

Is there a way to have my formulas reference a single cell value so that I can change multiple formulas with the one cell?

From Dev

Reference same object with different tags

From Dev

VBA Macro If cell found in multiple cells then

From Dev

Reference a cell of the previous row in the same table in Excel?

From Dev

Relative cell reference in Excel using different axis

From Dev

Worksheet_Change Macro - Changing multiple cells

From Dev

Reference a different column in that same table

From Dev

Excel Macro, Combining two Private Sub worksheet_change

From Dev

Reference a cell from another worksheet

From Dev

How to reference same cell in previous sheet in excel?

From Dev

Reference worksheet by cell value?

From Dev

Exclude Cell Range from Worksheet_Change function

From Dev

Excel hyperlink follow macro after worksheet_change event

From Dev

Combining multiple Worksheet_Change macros

From Dev

Excel: Different formatting on different values in same cell

From Dev

VBA Trying to make WorkSheet_Change work on multiple sheets but not all

From Dev

Getting error while running macro on different worksheet but on same workbook

From Dev

Excel Macro: If cell = "x", copy multiple cells from other worksheet

From Dev

How to unable Worksheet_Change from Excel VBA if Macro is enabled

From Dev

Combining Select Case as Cell and Range in Worksheet_Change

From Dev

Transfer cell values from different columns and sheets from multiple excel files with same structure into a single dataframe

Related Related

  1. 1

    Worksheet_Change - Targeting multiple cells simultaneously

  2. 2

    link Open/Libre Office button to cell and reference cell in macro

  3. 3

    How to change cell value using Worksheet_change event without triggering a second call

  4. 4

    Excel hyperlink follow macro after worksheet_change event

  5. 5

    Worksheet_change macro running multiple times in excel 2007

  6. 6

    MATLAB: How to create cell arrays with multiple values in correspondence of the same reference

  7. 7

    Combining multiple macros (worksheet_change)

  8. 8

    How to manually modify a cell that is being edited in a worksheet_change event?

  9. 9

    Is there a way to have my formulas reference a single cell value so that I can change multiple formulas with the one cell?

  10. 10

    Reference same object with different tags

  11. 11

    VBA Macro If cell found in multiple cells then

  12. 12

    Reference a cell of the previous row in the same table in Excel?

  13. 13

    Relative cell reference in Excel using different axis

  14. 14

    Worksheet_Change Macro - Changing multiple cells

  15. 15

    Reference a different column in that same table

  16. 16

    Excel Macro, Combining two Private Sub worksheet_change

  17. 17

    Reference a cell from another worksheet

  18. 18

    How to reference same cell in previous sheet in excel?

  19. 19

    Reference worksheet by cell value?

  20. 20

    Exclude Cell Range from Worksheet_Change function

  21. 21

    Excel hyperlink follow macro after worksheet_change event

  22. 22

    Combining multiple Worksheet_Change macros

  23. 23

    Excel: Different formatting on different values in same cell

  24. 24

    VBA Trying to make WorkSheet_Change work on multiple sheets but not all

  25. 25

    Getting error while running macro on different worksheet but on same workbook

  26. 26

    Excel Macro: If cell = "x", copy multiple cells from other worksheet

  27. 27

    How to unable Worksheet_Change from Excel VBA if Macro is enabled

  28. 28

    Combining Select Case as Cell and Range in Worksheet_Change

  29. 29

    Transfer cell values from different columns and sheets from multiple excel files with same structure into a single dataframe

HotTag

Archive