Help with VBA code

[Dankata]

I would like to ask for some help with functionality that I want to introduce in my Excel file: I want to have some cells active/inactive depending on a value in another cell. Inactive means that the cells will be visible but they will be grey shaded and people will not be able to write in them.
I have a number of indicators with targets and results, and depending whether the result is above or below the target I want some other cells to become active and people can introduce information therein.

Let's take the following example:
Column A – Indicators
A2 – Number of cars produced
A3 – Number of people employed
A4 – Productivity ratio
Column B – Target
B2 – 1000
B3 – 50
B4 – 5%
Column C – Results
C2 – 1100 (the objective is that it should be higher than the target)
C3 – 54 (the objective is that it should be lower than the target)
C4 – 4.91% (the objective is that it should be higher than the target)

I have also cells (D2:L4) that are inactive when the results are fine. But when they are not in line with the target (in my case C3 & C4), the respective cells (D3:L3 and D4:L4) should become active and users be able to include additional information in the cells D2:L4. I would like the activation of cells to happen automatically, i.e. without the need to press Run Macros or anything else.

I tried with Data Validation but this is not exactly what I am looking for. From what I read I understand that I have to use VBA but unfortunately, I am not expert in this. I am not sure also how the protection works in this case – I would like after users introduce the information in cells D2:L4 that it is available to others for editing and copy/paste.

I would really appreciate some help with all this.

 

[p9j123]

Try this...

right click on sheet1 tab - view code -> paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C4")) Is Nothing Then
       Application.Run ("rod")
           End If
End Sub

Then on standard module, paste the below code....

Sub rod()
ActiveSheet.Unprotect
Range("D2:L4").Select
Selection.Locked = True
Range("D2:L4").Interior.Color = RGB(205, 201, 201)
ActiveSheet.Protect


If Cells(2, 3).Value < Cells(2, 2).Value Then
ActiveSheet.Unprotect


Range("D2:L2").Select
Selection.Locked = False
Range("D2:L2").Interior.Color = RGB(255, 250, 250)


End If


If Cells(3, 3).Value > Cells(3, 2).Value Then
ActiveSheet.Unprotect
Range("D3:L3").Select
Selection.Locked = False


Range("D3:L3").Interior.Color = RGB(255, 250, 250)


End If


If Cells(4, 3).Value < Cells(4, 2).Value Then
ActiveSheet.Unprotect
Range("D4:L4").Select
Selection.Locked = False


Range("D4:L4").Interior.Color = RGB(255, 250, 250)


End If
ActiveSheet.Protect
Range("a1").Activate
End Sub

 

[Dankata]

p9j123,
Thank you for the swift reply.
I tested it and it works but I would like column C to be left unprotected at all times.
Maybe I was not clear enough in my post above - I need protect/unprotect only for the range of cells D:L.
For the rest there is no need for protection.
From what I tested, I saw that the entire worksheet is protected except for the range of cells (D:L) where the relevant condition (result vs. target) has been met.
If there is a requirement to protect the entire worksheet, could we leave two columns unprotected always?
This will allow users to enter data regularly and based on the value entered the macros will run automatically.

Also, if the condition for a particular indicator is set to IF C(result) < B(target) then unprotect (row 4 in my example), can we ignore blank values and keep the D:L range protected? I tested the code with a blank value and it leaves the row 4 unprotected which should be avoided. A text entry unprotecting the D:L range of cells should also be avoided - if for example a user enters 'n.a.' in column C, this should not trigger the Macros to unprotect the cells.

Last question, can I hide (or protect) the VBA code that you send me from the other users of the file? I do not want someone changing it by whatever reason.

Thank a lot again.

 

[Dankata]

I would like to clarify my last post and particularly the paragraph about blank values, here it is again with the clarification in red:

Also, if the condition for a particular indicator is set to IF C(result) < B(target) is true then unprotect (row 4 in my example), can we ignore blank values in column C and keep the D:L range protected? I tested the code with a blank value in C4 and it leaves the D4:L4 unprotected which should be avoided. A text entry in column C unprotecting the D:L range of cells should also be avoided - if for example a user enters 'n.a.' in column C, this should not trigger the Macros to unprotect the cells.

 

[p9j123]

I tested it and it works but I would like column C to be left unprotected at all times.
Maybe I was not clear enough in my post above - I need protect/unprotect only for the range of cells D:L.
For the rest there is no need for protection.
From what I tested, I saw that the entire worksheet is protected except for the range of cells (D:L) where the relevant condition (result vs. target) has been met.
If there is a requirement to protect the entire worksheet, could we leave two columns unprotected always?
This will allow users to enter data regularly and based on the value entered the macros will run automatically.

Select all cell, ctrl+A but ensure that everything is highlighted, right click - > Format Cells -> Protection Tab ->untick LOCKED then okay.

Select D2 to D4 the follow the above step again the this time ensure that LOCKED is ticked.

Then lock the sheet by pressing ALT+T+P+P

Also, if the condition for a particular indicator is set to IF C(result) < B(target) then unprotect (row 4 in my example), can we ignore blank values and keep the D:L range protected?

updated macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C4")) Is Nothing Then
       Application.Run ("blank")
           End If
End Sub

And:

Sub rod()
ActiveSheet.Unprotect
Range("D2:L4").Select
Selection.Locked = True
Range("D2:L4").Interior.Color = RGB(205, 201, 201)
ActiveSheet.Protect


If Cells(2, 3).Value < Cells(2, 2).Value Then
ActiveSheet.Unprotect


Range("D2:L2").Select
Selection.Locked = False
Range("D2:L2").Interior.Color = RGB(255, 250, 250)


End If


If Cells(3, 3).Value > Cells(3, 2).Value Then
ActiveSheet.Unprotect
Range("D3:L3").Select
Selection.Locked = False


Range("D3:L3").Interior.Color = RGB(255, 250, 250)


End If


If Cells(4, 3).Value < Cells(4, 2).Value Then
ActiveSheet.Unprotect
Range("D4:L4").Select
Selection.Locked = False


Range("D4:L4").Interior.Color = RGB(255, 250, 250)


End If
ActiveSheet.Protect
Range("a1").Activate
End Sub


Sub blank()
Dim rngCheck As Range:  Set rngCheck = ActiveSheet.Range("C2:C4")
    
    Dim iCell As Range
    For Each iCell In rngCheck
        If IsEmpty(iCell) Then
            iCell.Select
           Exit Sub
        End If
    Next iCell
Application.Run ("rod")
       
End Sub

I tested the code with a blank value and it leaves the row 4 unprotected which should be avoided. A text entry unprotecting the D:L range of cells should also be avoided - if for example a user enters 'n.a.' in column C, this should not trigger the Macros to unprotect the cells.

Select cells C2 to C4 then on menu ->Data->Data Validation-> on Allow select Decimal, Min = 0.0001 Max = 10000 then OK

Last question, can I hide (or protect) the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code that you send me from the other users of the file? I do not want someone changing it by whatever reason.

On the source code right click the VBAProject(FileName) ->VBAProject Properties - > Protection Tab - > Tick Locke Project for Viewing then set your password.

 

[Dankata]

I tested it, everything works really fine. This is exactly what I need. Thanks a lot!

Now, I have a file with 7 sheets and I need this functionality working in all of them. The volume of information across the sheets is different – in some of them I have approximately 40 indicators and relevant values, in other sheets I have not more than 10.
My question is: how do I reproduce the code that you sent me to the other 6 sheets?

New modules?
Or for each sheet I introduce new codes?
I tried to have the macros working also for another sheet - I repeated the steps for the first code only in the second sheet but all I achieved was to protect (inactivate) the D2:L4 range in the second sheet. And they do not unprotect (activate) regardless of the values in columns B&C.
But now that I think what I tried doesn't make sense because the values and conditions (result vs. target) are different in the different sheets, so probably I need 7 different codes ("rod"). How do I introduce and link them?
The conditions inside (result vs. target -> protect/unprotect D:L) are not difficult to do based on the code that you provided me already.

 

[p9j123]

it will be great if you can share your working file.

 

[Dankata]

How do I share files in this forum? I couldn't find such an option.