Remove part of row based on cell value

[Gwill1983]

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

 

[mumps]

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.

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