Any one can help me to correct below VBA

Thilan

New Member
Joined
Jul 13, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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
VBA 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
 
Last edited by a moderator:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,975
Office Version
  1. 2010
Platform
  1. Windows
to fix items 3 and 4 try changing
VBA Code:
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
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,218
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,140,997
Messages
5,703,633
Members
421,307
Latest member
morrden86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top