Macro won't run with Option Explicit

Andrew B

New Member
Joined
Jan 21, 2011
Messages
27
I have a macro that changes the font color and font size of a cell. It is located in a module that contains several other macros. If I declare Option Explicit in that module, the macro will not run. I get an "compile error" message stating that the variable "font_size" is is not defined. If I remove Option Explicit, the macro runs, but my limited experience with VBA macros tells me that this isn't right, that I should fix the code so that it will run with Option Explicit declared. The problem is, I don't know how to fix it. Can anyone help me. Here is the code:
Code:
Sub BlackToRed()
Dim font_colour As Integer
Dim B As Variant
font_colour = 1
font_size = 10

ActiveSheet.Unprotect Password:="myPWD"
ActiveCell.Font.ColorIndex = font_colour
ActiveCell.Font.Size = font_size

If font_colour = 1 Then
font_colour = 3
End If

If font_size = 10 Then
font_size = 11
End If


Selection.Font.ColorIndex = font_colour
ActiveCell.Font.Size = font_size
ActiveSheet.Protect Password:="myPWD"
End Sub
I'm stumped!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Try

Rich (BB code):
Sub BlackToRed()
Dim font_colour As Integer, font_size As Integer
font_colour = 1
font_size = 10

ActiveSheet.Unprotect Password:="myPWD"
ActiveCell.Font.ColorIndex = font_colour
ActiveCell.Font.Size = font_size

If font_colour = 1 Then
font_colour = 3
End If

If font_size = 10 Then
font_size = 11
End If


Selection.Font.ColorIndex = font_colour
ActiveCell.Font.Size = font_size
ActiveSheet.Protect Password:="myPWD"
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top