[xpost][/xpost]
I'm trying to achieve below,
1. Allow specific text as valid input - working fine
2. If "DO" is entered, the cell needs to be green - working fine
3. If "DO" is deleted, the cell colour need to be blank - not working
4. Cell formating(border, font size) should not be able to change - not working
5. need to set C20:C30 as text only in the same sheet- code not added
6. hide entire raw if cells in column A is blank - code not added
this is my code added in sheet 1
Code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Changed As Range, c As Range
Dim sCleared As String
Set Changed = Intersect(Target, Range("D29:AH40,D50:AH62"))
If Not Changed Is Nothing Then
Application.EnableEvents = False
For Each c In Changed
Select Case c.Value
Case "AB", "DO", "SL", "CV", "DT", "EX", "MT", "NJ", "PV", "RN", "SL", "UM", "UP", "LD", "TF", "ab", "do", "cv", "dt", "ex", "mt", "nj", "pv", "rn", "sl", "um", "up", "ld", "tf", -10 To 20
Range("D29:AH40,D50:AH62").Borders.LineStyle = xlContinuous
Case Else
sCleared = sCleared & vbLf & c.Address(0, 0) & " (" & c.Value & ")"
c.ClearContents
Range("D29:AH40,D50:AH62").Borders.LineStyle = xlContinuous
End Select
Next c
Application.EnableEvents = True
If Len(sCleared) > 0 Then MsgBox "Invalid entry"
End If
If Target.HasFormula Then Exit Sub
Application.EnableEvents = False
Target = UCase(Target.Cells(1))
Application.EnableEvents = True
For Each cell In Range("D29:AH40,D50:AH62")
If cell.Value = "DO" Then
cell.Interior.Color = XlRgbColor.rgbLightGreen
End If
Next
End Sub