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
|