I have the following code

Private Sub Worksheet_Change(ByVal Target as Range) 
Target.Font.ColorIndex = 5 
End Sub

I want to make this code work in the worksheet, but it won't save as a macro or run because the module only accepts the Sub () End Sub syntax. What do I do?

  • Is this an .xlsm file or .xlsx? – harrymc Feb 12 at 12:47
  • it's a '.xlsm' @harrymc – anne Feb 12 at 13:57
  • 1
    What exactly happens? – harrymc Feb 12 at 15:13
  • 2
    I suspect an XY problem. What is it you are actually trying to accomplish? – cybernetic.nomad Feb 12 at 18:02
  • @harrymc when i write the code in ThisWorksheet and run it it asks to name a macro, I do that, then, when it makes a code in the module, it makes a 'Sub() End Sub' code and if i change that to the code I mentioned it asks to make a new macro, then when I do that it makes yet a new 'Sub() End Sub' syntax. – anne Feb 13 at 7:54

The Worksheet.Change event is an event routine, not a macro that can be run manually.

As specified in the documentation, it will run automatically "when cells on the worksheet are changed by the user or by an external link".

To test the routine, you need to modify the value of the cell(s).

  • thank you, but now i have another question: i tried using the code as a button and if the cells change i wanted it t do something and when i clicked the button it gave an error because there's something in the brackets of Private Sub (). how do i fix this? @harrymc – anne Feb 13 at 11:25
  • Better create a separate post for this question with all detail such as a screenshot of the error. – harrymc Feb 13 at 11:27
  • all right. thank you. – anne Feb 13 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.