Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple values in Private Sub Worksheet_Change(ByVal Target As R | Excel Discussion (Misc queries) | |||
Control Toolbox and Private Sub Worksheet_Change(ByVal Target As R | Excel Discussion (Misc queries) | |||
Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming |