10004991
04-28-2021, 06:58 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C14" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
ActiveSheet.Unprotect Password:="crazyhorse"
Rows("21:137").Select
Selection.EntireRow.Hidden = False
With ActiveSheet
For i = 21 To 137
If Cells(i, 3) = "NOT APPLICABLE" Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next
End With
Range("C14:E14").Select
Columns("C:O").Select
Selection.EntireColumn.Hidden = False
With ActiveSheet
For i = 3 To 15
If Cells(21, i) = "NOT APPLICABLE" Then
Cells(1, i).EntireColumn.Hidden = True
End If
Next
End With
Range("C14").Select
ActiveSheet.Protect Password:="crazyhorse"
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim fullRange As Range
Set fullRange = Worksheets("DR-976A-English").Range("C21:O74")
For Each cell In fullRange
If Cell.Value Is > 550 Then
cell.Interior.ColorIndex = 3
elseif Cell.Value Is < 450 Then
cell.Interior.ColorIndex = 3
Else
cell.Interior.ColorIndex = xlNone
Next
End Sub
-------------------------------------------------------------------------------------------------------------------------------------------------
Hello
Second code is effecting the first code
First code works as is and changes the displayed arrays so that data can be entered. This is controlled by a drop down list at cell C14
Second code is to highlight cells less than 450 and greater than 550 red. It works except is causes the first code to stop functioning properly. The first code gets stuck on whatever was selected within cell C14 so that the array displayed is not correct.
For example:
Dropdown selection IP36 displays a range of C21:H32
If I try to change to CA84 which is a range of C21:I44 the correct formatting for CA84 appears but only the range C21:H32
Would love some advice!
Thanks!
Const WS_RANGE As String = "C14" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
ActiveSheet.Unprotect Password:="crazyhorse"
Rows("21:137").Select
Selection.EntireRow.Hidden = False
With ActiveSheet
For i = 21 To 137
If Cells(i, 3) = "NOT APPLICABLE" Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next
End With
Range("C14:E14").Select
Columns("C:O").Select
Selection.EntireColumn.Hidden = False
With ActiveSheet
For i = 3 To 15
If Cells(21, i) = "NOT APPLICABLE" Then
Cells(1, i).EntireColumn.Hidden = True
End If
Next
End With
Range("C14").Select
ActiveSheet.Protect Password:="crazyhorse"
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim fullRange As Range
Set fullRange = Worksheets("DR-976A-English").Range("C21:O74")
For Each cell In fullRange
If Cell.Value Is > 550 Then
cell.Interior.ColorIndex = 3
elseif Cell.Value Is < 450 Then
cell.Interior.ColorIndex = 3
Else
cell.Interior.ColorIndex = xlNone
Next
End Sub
-------------------------------------------------------------------------------------------------------------------------------------------------
Hello
Second code is effecting the first code
First code works as is and changes the displayed arrays so that data can be entered. This is controlled by a drop down list at cell C14
Second code is to highlight cells less than 450 and greater than 550 red. It works except is causes the first code to stop functioning properly. The first code gets stuck on whatever was selected within cell C14 so that the array displayed is not correct.
For example:
Dropdown selection IP36 displays a range of C21:H32
If I try to change to CA84 which is a range of C21:I44 the correct formatting for CA84 appears but only the range C21:H32
Would love some advice!
Thanks!