VBA

[Mustafa Evans]

Have never used VBA before. And I found this code online.

Private Sub Worksheet_Change(ByVal Target As Range) If Range("A1") = "Routine" Then Range("A3:A4").Locked = FALSE ElseIf Range("A1") = "Urgent" Then Range("A3:A4").Locked = True End If End Sub

It seems to allow me to lock cells dependent on a value in Cell A1.

I need the following to happen. If A1 is Routine then A2 is locked and the cell greyed out. And A3 and A4 is allowed to be edited and data entered.

However if A1 is Urgent cell A2 is unlocked and A3 and A4 and locked and greyed out.

If course I want this to occur throughout column A. Can anyone help. Thanks

 

[cooper645]

What about the following in he sheet code:

It assumes that the sheet is protected with the user allowed to select unlocked cells only and that Cell A1 is not locked.

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect

If Range("A1") = "Routine" Then

        With Range("A2")
            .Locked = True
            .Interior.ColorIndex = 15
        End With

                With Range("A3:A4")
                    .Locked = False
                    .Interior.Color = xlNone
                End With

ElseIf Range("A1") = "Urgent" Then

        With Range("A2")
            .Locked = False
            .Interior.Color = xlNone
        End With

                With Range("A3:A4")
                    .Locked = True
                    .Interior.ColorIndex = 15
                End With

End If

ActiveSheet.Protect
End Sub

 

[Mustafa Evans]

Thanks I'll try that when I get back into the office.

 

[Rick Rothstein]

@cooper645,

The OP will have to weigh in on this, but I was just wondering if your If..Then block should have an Else block covering (I am assuming) a blank cell A1, maybe by locking cells A2:A4 and setting their color to xlNone?

 

[cooper645]

Good point, I set a data validation list of only those two options to be available in A1.

 

[Rick Rothstein]

I set a data validation list of only those two options

In that case, I believe the following shorter untested (because I am about to go to sleep) Change event code will also work...

Private Sub Worksheet_Change(ByVal Target As Range)
  ActiveSheet.Unprotect
  [A2].Locked = [A1="Routine"]
  [A2].Interior.ColorIndex = Array(15, xlNone)([A1="Routine"])
  [A3:A4].Locked = Not Range("A2").Locked
  [A2:A4].Interior.Color = Array(xlNone, 15)([A1="Routine"])
  ActiveSheet.Protect
End Sub

 

[Mustafa Evans]

Not sure why it didn't work. I've changed my spreadsheet now to try to implement this change. G column (can be either "Routine" or "Urgent")

If appointment is "Routine" H column should be grey and locked.
If appointment is "Urgent" I & J Columns should be greyed and locked.

Columns H, I & J are populated with dates. I also need these dates to show dates that are about to expire (5 days) and the cell should be AMBER and if expired the cell should be RED.

I need these changes to happen on all workbooks within the spreadsheet.

Many thanks again in advance.

 

[Mustafa Evans]

Am afraid for whatever reason the suggestions received already didn't seem to work. Hope someone can help, i've no clue about VBA but am told that it should help me problem. I have a number of Columns in excel that hold the following data types:

Column G - Text, limited to either Routine or Urgent
Column H, I, J - Dates (dd/mm/yyyy)

If Column G appointment is "Routine" Column H should be grey and locked.
If Column G appointment is "Urgent" Columns I & J should be greyed and locked.

Columns H, I & J are populated with dates. I also need these dates to show dates that are about to expire (5 days) and the cell should be AMBER and if expired the cell should be RED.

I need these changes to happen on all workbooks within the spreadsheet.

Many thanks again in advance.

 

[Mustafa Evans]

Have tried to adapt the code i was given (above) however it sort of works, except it either locks the whole sheet after an entry or if "Routine" is selected H8 is greyed out (which is correct), but if the type changes to "Urgent" H8 remains greyed out (incorrect) and I8:J8 are greyed out (which is correct).

The adapted code is :

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect

If Range("G8") = "Routine" Then
With Range("H8")

.Locked = True

.Interior.ColorIndex = 15

End With

With Range("I8:J8")

.Locked = False

.Interior.Color = xlNone

End With

ElseIf Range("G8") = "Urgent" Then

With Range("G8")

.Locked = False

.Interior.Color = xlNone

End With
With Range("I8:J8")
.Locked = True

.Interior.ColorIndex = 15

End With

End If

ActiveSheet.Protect

End Sub

---
Hope someone can help me, i will need this code to be utilised from cell G8 down so G9, G10, etc.

Many thanks again.

 

[Mustafa Evans]

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Unprotect

If Range("G8") = "Routine" Then
With Range("H8")

.Locked = True

.Interior.ColorIndex = 15

End With

With Range("I8:J8")

.Locked = False

.Interior.Color = xlNone

End With

ElseIf Range("G8") = "Urgent" Then

With Range("G8")

.Locked = False

.Interior.Color = xlNone

End With
With Range("I8:J8")
.Locked = True

.Interior.ColorIndex = 15

End With

End If

ActiveSheet.Protect

End Sub