Any one can help me to correct below VBA

[Thilan]

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

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

 

[offthelip]

to fix items 3 and 4 try changing

For Each cell In Range("D29:AH40,D50:AH62")
If cell.Value = "DO" Then
cell.Interior.Color = XlRgbColor.rgbLightGreen
End If
Next
End Sub

to

For Each cell In Range("D29:AH40,D50:AH62")
If cell.Value = "DO" Then
cell.Interior.Color = XlRgbColor.rgbLightGreen
Else
cell.Interior.Color = xlNone
End If
Next
Range("D29:AH40,D50:AH62").Borders.LineStyle = xlContinuous
End Sub

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Any one can help me to correct below VBA - OzGrid Free Excel/VBA Help Forum
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.