Why not simply use Conditional Formatting, no code required.
I want to use VBA as this sheet's data-filled by users
Posts by thisumi
-
-
[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
Display MorePrivate 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