Excel VBA-- Creating macro to change cell color and call other 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(s) 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 bring their cursor over cell F25 and clicks on cell F25, it will take them to the macro associated with the value 1000EFG. I have already created the other macros associated with those particular values, I just need the cells in the F column to change color and become clickable to the user and when clicked, call a certain macro. I will post the code I have tried below. ANY help is very much appreciated. You guys are awesome, Thanks!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim c As Range
  c = Range("D7:D446")
  For Each c In Intersect(ActiveCell, Range("D7:D446")) 'this is where the error is occuring
    Select Case c.Value
     Case "1000GP", "1000MM", "19FEST", "20IEDU", "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, -3).Value2
        Case "1000GP": gotoref1
        Case "1000MM": gotoref2
        Case "19FEST": gotoref3
        Case "20IEDU": gotoref4
        Case "20ONLC": gotoref5
        Case "20PART": gotoref6
        Case "20PRDV": gotoref7
        Case "20SPPR": gotoref8
        Case "22DANC": gotoref9
        Case "22LFLC": gotoref10
        Case "22MEDA": gotoref11
        Case "530CCH": gotoref12
        Case "60PUBL": gotoref13
        Case "74GA01": gotoref14
        Case "74GA17": gotoref15
        Case "74GA99": gotoref16
        Case "78REDV": gotoref17
      End Select
  End If
End Sub

After you dimension the object 'c' as Range(...), you should use the keyword SET. Make the next line SET c = Range("D7:D446") — Ryan B.
– Ryan B., Commented Jun 5, 2017 at 20:27
Also, I think your loop is curious. You might want to use Target and not ActiveCell. Usually a cell is changed by the user, so these cells will be the same, and so you get the same behavior either way. But it can happen that a cell's value is changed by other means, and then the cell that's raising the event won't be the active cell. — Ryan B.
– Ryan B., Commented Jun 5, 2017 at 20:30
I was going to suggest that you remove the line c = Range("D7:D446"), as it will just generate an error, and you are resetting the value of c on the very next line anyway. — YowE3K
– YowE3K, Commented Jun 5, 2017 at 20:30
If @Greenspark first comment doesn't solve the issue, please tell us what issue(s) you are having. It will help us help you a lot more. — Scott Holtzman
– Scott Holtzman, Commented Jun 5, 2017 at 20:31
@Andrew - are you sure you are changing values in Range("D7:D466"). If something outside that range changes your code will bomb with object required error. You can test this with If Not Intersect(Target,Range("D7:D466")) is Nothing Then ... your code ... End If — Scott Holtzman
– Scott Holtzman, Commented Jun 5, 2017 at 20:55

Scott Holtzman · Accepted Answer · 2017-06-05 21:00:37Z

1

Adjust the Worksheet_Change event For structure to this:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Range("D7:D446")) is Nothing Then 

      Dim c As Range

      For Each c In Target 

          Select Case c.Value
              Case "1000GP", "1000MM", "19FEST", "20IEDU", "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 If

End Sub

answered Jun 5, 2017 at 21:00

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

anve Over a year ago

excellent. Thank you so much! That code fix changes the cell color to red. However, the cell in the F column is still not clickable to the user. So, when the user clicks on the red cell in column F, it does not take them to the macro associated with one of those 17 values. That's what the second event is for. the Private Sub Worksheet_BeforeDoubleClick. Any suggestions? A big thank you, again!

Scott Holtzman Over a year ago

I suggest posting a new question. But before you do, answer these questions for yourself: 1) Are you actually double-clicking? 2) Are the macro names actually gotoref1? 3) are those macros listed as private on another worksheet?

Collectives™ on Stack Overflow

Excel VBA-- Creating macro to change cell color and call other macros based on cell value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related