Excel VBA Macro calling multiple Macros based on cell value

Question

I am trying to write a macro that calls different macros and changes cell colors. So, if a cell in the entire column D (D4:D446) equals a certain value, THIS macro will call a separate macro associated with that value.

In other words, What I want is, for example, if any or multiple cells in range D7:D446 = "1000ABC", "1000EFG", or "1000HIJ", any/all cells in column F7:F446 will turn red to indicate to the user that they need to click on that cell in F7:F446 and when the user clicks on that cell in column F, it will call the correct macro I already created.

Example: if cell D25="1000EFG" cell F25 will turn red and when the user clicks on cell F25, it will take him to the macro associated with the value 1000EFG. I have already created the other macros, I just need to tie them together with this functionality. (These values are hypothetical)

The issues I am having is that no matter the values in cell D, when I click on the associated cell in the F column, it will take me to one macro and one macro only (not the correct macro associated with the value in cell D). I also am unsure of the syntax on how to change a cell color red depending on a value. I can't seem to tie these functionalities together in one macro. I will post the code I have tried below. ANY help is very much appreciated. You guys are awesome, Thanks!

Sub gotorefs()

For Each c In Worksheets("JE").Range("D7:D446")

    If c.Value = "1000GP" Then
        Call gotoref1
     Worksheets("JE").Range("F7:F446").Select.ActiveCell.Interior.ColorIndex = 3

ElseIf c.Value = "1000MM" Then
        Call gotoref2
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

 ElseIf c.Value = "19FEST" Then
        Call gotoref3
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "20IEDU" Then
        Call gotoref4
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

 ElseIf c.Value = "20ONLC" Then
        Call gotoref5
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "20PART" Then
        Call gotoref6
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "20PRDV" Then
        Call gotoref7
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "20SPPR" Then
        Call gotoref8
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "22DANC" Then
        Call gotoref9
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "22LFLC" Then
        Call gotoref10
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "22MEDA" Then
        Call gotoref11
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "530CCH" Then
        Call gotoref12
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "60PUBL" Then
        Call gotoref13
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "74GA01" Then
        Call gotoref14
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "74GA17" Then
        Call gotoref15
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "74GA99" Then
        Call gotoref16
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3

ElseIf c.Value = "78REDV" Then
        Call gotoref17
        Worksheets("JE").Range("F7:F446").Cell.Interior.ColorIndex = 3
End If

Next c

End sub

You have syntax error here Worksheets("JE").Range("F7:F446").Select.ActiveCell.Interior.ColorIndex = 3. Apart of that, your loop tries to call all the subs in turn on the rows, while you said you want to do it only when the user clicks on the F cell. — A.S.H
I should clarify, if, for example, cell D25 = "1000GP", once that value is entered into cell D25, I would like F25 to turn red automatically. Once the user clicks on the red colored cell F25, it will take them to the macor associated with the value "1000GP", which is macro "gotoref1". — anve
Then your code should be split in two parts. The above chunk should only set the colors in column F. another one, a Worksheet_Change event should capture the click on cell F and call the appropriate routine. Besides, I advice you to use some naming convention for those routines, such as Sub1000GP, this will make life easier for you later. — A.S.H
Also, as you can probably tell, I am unsure of the syntax in order to execute this functionality. I haven't had much luck with researching it online and this is my first experience with vba. — anve
So, as of right now, I have the "sub gotorefs()". Do you suggest I keep this sub, and just add a private sub underneath that changes the cell color, THEN in the same sub gotorefs(), under the private sub, create the method that calls the macros associated with the different values? — anve

A.S.H A.S.H · Accepted Answer · 2017-06-02T19:33:03

Try to do it by handling the Worksheet_Change event. To do so, add this to your worksheet "JE" code module:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range: Set c = Range("D7:D446")
  For Each c In c.Cells
    Select Case c.Value
      Case "1000GP", "1000MM", "19FEST", "20IEDU", "20ONLC", "20PART", "20PRDV", "20SPPR", "22DANC", "22LFLC", "22MEDA", "530CCH", "60POUBL", "74GA01", "74GA17", "74GA99", "78REDV"
        Cells(c.row, "F").Interior.ColorIndex = 3
      Case Else
        Cells(c.row, "F").Interior.ColorIndex = 0
    End Select
  Next c
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column = 6 And Target.Cells.count = 1 And Target.Interior.ColorIndex = 3 Then
    Cancel = True
    ' Now call the appropriate routine according to column C
    Select Case Target.Offset(0, -2).Value2
      Case "1000GP": gotoref1
      Case "1000MM": gotoref2
      Case "19FEST": gotoref3
      Case "20PRDV": gotoref4
      Case "20IEDU": gotoref5
      Case "20ONLC": gotoref6
      Case "20PART": gotoref7
      Case "20SPPR": gotoref8
      Case "22DANC": gotoref9
      Case "22LFLC": gotoref10
      Case "22MEDA": gotoref11
      Case "530CCH": gotoref12
      Case "60POUBL": gotoref13
      Case "74GA01": gotoref14
      Case "74GA17": gotoref15
      Case "74GA99": gotoref16
      Case "78REDV": gotoref17
    End Select
  End If
End Sub

A final advice, this way of dispatching to so many different routines is tedious and error prone. You might think of following some naming convention to make the called routines match with the values. For example, if you name your routines like Ref_1000GP, Ref_1000MM etc, then the second Select Case statement would reduce to a single line like the following:

CallByName "Ref_" & Target.Offset(0, -3).Value2

Excel VBA Macro calling multiple Macros based on cell value

1 Answers