Private Sub Worksheet_Change doesn't work?
 

I have this Worsheet_Change code in worksheet Sheet2 (named A1) which used to
work but not anymore.
I've checked the VBA is not in design mode. All other codes in the same
sheet work, except the worksheet change code. What's wrong?

Private Sub Worksheet_Change(ByVal Target As Range)

' Change interior color of cells

Dim MyWkSht As Worksheet
Dim HighlighRange1 As Range, HighlightRange2 As Range

Set MyWkSht = ThisWorkbook.Worksheets("A1")

MyWkSht.Range("A31").Interior.ColorIndex = 36

Set HighlightRange1 =
MyWkSht.Range("E31:T31,U31:AC40,AD31:AG40,AJ31:AL4 0,U19:AC19,AD19:AG28,AJ19:AL28,R43:T43,U43:AC52,AD 43:AG52,AJ43:AL52,A32")

If MyWkSht.Range("A31").Value < "" Then
HighlightRange1.Interior.ColorIndex = 36

Else
HighlightRange1.Interior.ColorIndex = xlNone

End If

For n = 1 To 9

Set r1 = MyWkSht.Range("E" & n + 31 & ":" & "R" & n + 31)
Set r2 = MyWkSht.Range("U" & n + 19)
Set r3 = MyWkSht.Range("R" & n + 43)
Set r4 = MyWkSht.Range("A" & n + 32)
Set HighlightRange2 = Union(r1, r2, r3, r4)

If MyWkSht.Range("A" & n + 31).Value < "" Then
HighlightRange2.Interior.ColorIndex = 36

Else
HighlightRange2.Interior.ColorIndex = xlNone

End If

Next n

MyWkSht.Range("A41").Interior.ColorIndex = xlNone

' Toggle the visibilities of the command buttons on Sheet1 and Sheet2

If MyWkSht.Range("AW8").Value = "" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
cbInputB1.Enabled = False
cbOutput.Enabled = False
cbOutput.Left = 216

ElseIf MyWkSht.Range("AW8").Value = "Yes" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
Sheet1.cbInputB2.Visible = False
Sheet1.cbInputB3.Visible = False
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 216
Sheet1.cbOutput.Left = 216

Else
cbInputB2.Visible = True
cbInputB3.Visible = True
Sheet1.cbInputB2.Visible = True
Sheet1.cbInputB3.Visible = True
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 432
Sheet1.cbOutput.Left = 432

End If

End Sub

Private Sub Worksheet_Change doesn't work?
 

Dim HighlighRange1 As Range

You have a typo in the posted version above. Don't know if it is in the
original or not.
That would not cause the code not to execute.

What exactly do you mean by don't work. Do you get an error message or just
no results. If it is just no results, you need to check your criteria cells
for your If statements to make sure the do or do not contain what you think
they should. Then step through the procedure by setting a breakpoint after
the first step then check one step at a time to see where it fails.


"Sam Kuo" wrote:

I have this Worsheet_Change code in worksheet Sheet2 (named A1) which used to
work but not anymore.
I've checked the VBA is not in design mode. All other codes in the same
sheet work, except the worksheet change code. What's wrong?

Private Sub Worksheet_Change(ByVal Target As Range)

' Change interior color of cells

Dim MyWkSht As Worksheet
Dim HighlighRange1 As Range, HighlightRange2 As Range

Set MyWkSht = ThisWorkbook.Worksheets("A1")

MyWkSht.Range("A31").Interior.ColorIndex = 36

Set HighlightRange1 =
MyWkSht.Range("E31:T31,U31:AC40,AD31:AG40,AJ31:AL4 0,U19:AC19,AD19:AG28,AJ19:AL28,R43:T43,U43:AC52,AD 43:AG52,AJ43:AL52,A32")

If MyWkSht.Range("A31").Value < "" Then
HighlightRange1.Interior.ColorIndex = 36

Else
HighlightRange1.Interior.ColorIndex = xlNone

End If

For n = 1 To 9

Set r1 = MyWkSht.Range("E" & n + 31 & ":" & "R" & n + 31)
Set r2 = MyWkSht.Range("U" & n + 19)
Set r3 = MyWkSht.Range("R" & n + 43)
Set r4 = MyWkSht.Range("A" & n + 32)
Set HighlightRange2 = Union(r1, r2, r3, r4)

If MyWkSht.Range("A" & n + 31).Value < "" Then
HighlightRange2.Interior.ColorIndex = 36

Else
HighlightRange2.Interior.ColorIndex = xlNone

End If

Next n

MyWkSht.Range("A41").Interior.ColorIndex = xlNone

' Toggle the visibilities of the command buttons on Sheet1 and Sheet2

If MyWkSht.Range("AW8").Value = "" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
cbInputB1.Enabled = False
cbOutput.Enabled = False
cbOutput.Left = 216

ElseIf MyWkSht.Range("AW8").Value = "Yes" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
Sheet1.cbInputB2.Visible = False
Sheet1.cbInputB3.Visible = False
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 216
Sheet1.cbOutput.Left = 216

Else
cbInputB2.Visible = True
cbInputB3.Visible = True
Sheet1.cbInputB2.Visible = True
Sheet1.cbInputB3.Visible = True
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 432
Sheet1.cbOutput.Left = 432

End If

End Sub

Private Sub Worksheet_Change doesn't work?
 

Hi JLGWhiz

Thanks for picking up the typo.
The worksheet change code in that particular worksheet just doesn't execute
when I posted this question, but somehow it's working again after I reopen
the workbook. I guess it might be the code changes I made to other linked
worksheet at the same time that confuse Excel. But thank god it's all good
now :-)

Thanks for your suggestion.

Sam

"JLGWhiz" wrote:

Dim HighlighRange1 As Range

You have a typo in the posted version above. Don't know if it is in the
original or not.
That would not cause the code not to execute.

What exactly do you mean by don't work. Do you get an error message or just
no results. If it is just no results, you need to check your criteria cells
for your If statements to make sure the do or do not contain what you think
they should. Then step through the procedure by setting a breakpoint after
the first step then check one step at a time to see where it fails.


"Sam Kuo" wrote:

I have this Worsheet_Change code in worksheet Sheet2 (named A1) which used to
work but not anymore.
I've checked the VBA is not in design mode. All other codes in the same
sheet work, except the worksheet change code. What's wrong?

Private Sub Worksheet_Change(ByVal Target As Range)

' Change interior color of cells

Dim MyWkSht As Worksheet
Dim HighlighRange1 As Range, HighlightRange2 As Range

Set MyWkSht = ThisWorkbook.Worksheets("A1")

MyWkSht.Range("A31").Interior.ColorIndex = 36

Set HighlightRange1 =
MyWkSht.Range("E31:T31,U31:AC40,AD31:AG40,AJ31:AL4 0,U19:AC19,AD19:AG28,AJ19:AL28,R43:T43,U43:AC52,AD 43:AG52,AJ43:AL52,A32")

If MyWkSht.Range("A31").Value < "" Then
HighlightRange1.Interior.ColorIndex = 36

Else
HighlightRange1.Interior.ColorIndex = xlNone

End If

For n = 1 To 9

Set r1 = MyWkSht.Range("E" & n + 31 & ":" & "R" & n + 31)
Set r2 = MyWkSht.Range("U" & n + 19)
Set r3 = MyWkSht.Range("R" & n + 43)
Set r4 = MyWkSht.Range("A" & n + 32)
Set HighlightRange2 = Union(r1, r2, r3, r4)

If MyWkSht.Range("A" & n + 31).Value < "" Then
HighlightRange2.Interior.ColorIndex = 36

Else
HighlightRange2.Interior.ColorIndex = xlNone

End If

Next n

MyWkSht.Range("A41").Interior.ColorIndex = xlNone

' Toggle the visibilities of the command buttons on Sheet1 and Sheet2

If MyWkSht.Range("AW8").Value = "" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
cbInputB1.Enabled = False
cbOutput.Enabled = False
cbOutput.Left = 216

ElseIf MyWkSht.Range("AW8").Value = "Yes" Then
cbInputB2.Visible = False
cbInputB3.Visible = False
Sheet1.cbInputB2.Visible = False
Sheet1.cbInputB3.Visible = False
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 216
Sheet1.cbOutput.Left = 216

Else
cbInputB2.Visible = True
cbInputB3.Visible = True
Sheet1.cbInputB2.Visible = True
Sheet1.cbInputB3.Visible = True
cbInputB1.Enabled = True
cbOutput.Enabled = True
cbOutput.Left = 432
Sheet1.cbOutput.Left = 432

End If

End Sub