Remove part of row based on cell value

Gwill1983

New Member
Joined
Oct 24, 2018
Messages
49
Hi all,

I have a spreadsheet whereby I need to keep track of quotation periods from various manufacturers. Some of these manufacturers have different rules to others and as such one standard set up will not work.

My first sheet is set up as below:

Column D = Manufacturer
Column I = Closing Date
Column J-P = Columns for quote extension dates

The second sheet is set up as below:

Column A = Manufacturer
Column B = Validity period
Column C = Quote can be extended? (y/n)

In essence, I would like to only allow columns J-P to be edited by the user if the manufacturer entered in column D allows the quote to be extended.
I don't care if this is to lock the cells, format the cells or hide them altogether.

I have researched as much as I can, but cannot find any way of changing part of a row depending on the value in a cell.

Does anybody have any ideas on how to achieve this?

Chris
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,003
Start by unlocking all the cells in Sheet1 and then protect the sheet. Place this macro in a standard module and run it from there. The macro assumes that the letter "y" will be in column C of Sheet2. Change the sheet names to suit your needs.
Code:
Sub lockRange()
    Application.ScreenUpdating = False
    Sheets("Sheet1").Unprotect
    Dim LastRow As Long, rng As Range, foundRng As Range
    LastRow = Sheets("Sheet2").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Sheets("Sheet2").Range("C2:C" & LastRow)
        If rng = "y" Then
            Set foundRng = Sheets("Sheet1").Range("D:D").Find(rng.Offset(0, -2), LookIn:=xlValues, lookat:=xlWhole)
            If Not foundRng Is Nothing Then
                Sheets("Sheet1").Range("J" & foundRng.Row).Resize(, 7).Locked = True
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
    Sheets("Sheet1").Protect
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,118
Messages
5,576,202
Members
412,706
Latest member
msousa25
Top