subhan1029
New Member
- Joined
- Mar 29, 2022
- Messages
- 4
- Office Version
- 2016
- Platform
- Windows
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 3 Then Exit Sub
ActiveSheet.Unprotect Password:="xxxx"
ActiveSheet.Cells.Locked = False
Select Case Target.Value
Case "Rectangular"
Range("D1,E1,I1,J1").Locked = True
Case "Circular"
Range("D1:G1").Locked = True
Case "ISA", "ISMC", "ISMB"
Range("E1").Locked = True
Range("G" & Target.Row).Resize(, 4).Locked = True
Case "Fastener"
Range("E" & Target.Row).Resize(, 6).Locked = True
Case "Flange"
Range("F" & Target.Row).Resize(, 5).Locked = True
Case "Pipes"
Range("G" & Target.Row).Resize(, 4).Locked = True
End Select
ActiveSheet.Protect Password:="xxxx"
ActiveSheet.EnableSelection = xlUnlockedCells
Application.ScreenUpdating = False
End Sub
Master BOQ - Copy - Copy.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | N | O | ||||
1 | |||||||||||||||||
2 | |||||||||||||||||
3 | ESTIMATION SHEET | ||||||||||||||||
4 | |||||||||||||||||
5 | |||||||||||||||||
6 | |||||||||||||||||
7 | Sr. No. | Description | C/S | Size | Sch / Type | L (mm) | W (mm) | Thk/Ht (mm) | OD (mm) | ID (mm) | MOC (mm) | Qty (Nos) | Weight (Kg) | Amount | |||
8 | 1 | Example | Rectangular | 1234 | 1234 | 1234 | 1234 | 1234 | |||||||||
9 | 2 | Example | Circular | 1234 | 1234 | 1234 | 1234 | 1234 | |||||||||
10 | 3 | Example | ISA | 65 x 65 x 8 | 1234 | 1234 | 1234 | 1234 | 1234 | 0.00 | |||||||
11 | 4 | Example | Pipes | _80NB | 20 | 1234 | 1234 | 1234 | 1234 | 1234 | 0.00 | ||||||
12 | 5 | Example | Fastner | 1234 | 1234 | 1234 | 1234 | 1234 | |||||||||
13 | 6 | Example | Circular | 1234 | 1234 | 1234 | 1234 | 1234 | |||||||||
14 | 7 | Example | Fastner | M12 x 65 | 1234 | 1234 | 1234 | #### | 1234 | 0.00 | |||||||
15 | 8 | Example | Flange | _.40NB | WNRF | 0.00 | |||||||||||
Sheet1 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A8:O17 | Cell | contains a blank value | text | NO |
It is perfectly working for "Flanges" onlyHi....
Above code is not working for me
please find attached image for reference
kindly suggest what to do
Master BOQ - Copy - Copy.xlsx
A B C D E F G H I J K L N O 1 2 3 ESTIMATION SHEET 4 5 6 7 Sr. No. Description C/S Size Sch / Type L (mm) W (mm) Thk/Ht (mm) OD (mm) ID (mm) MOC (mm) Qty (Nos) Weight (Kg) Amount 8 1 Example Rectangular 1234 1234 1234 1234 1234 9 2 Example Circular 1234 1234 1234 1234 1234 10 3 Example ISA 65 x 65 x 8 1234 1234 1234 1234 1234 0.00 11 4 Example Pipes _80NB 20 1234 1234 1234 1234 1234 0.00 12 5 Example Fastner 1234 1234 1234 1234 1234 13 6 Example Circular 1234 1234 1234 1234 1234 14 7 Example Fastner M12 x 65 1234 1234 1234 #### 1234 0.00 15 8 Example Flange _.40NB WNRF 0.00 Sheet1
Cells with Conditional Formatting Cell Condition Cell Format Stop If True A8:O17 Cell contains a blank value text NO