Lock Cells Based On Interior Colour Including Conditional Fomatting

[Admin]

Hi

Welcome to ExcelFox!!

Try this untested code. You have to define the color depending upon the other cells.

Dim Cell As Range
Dim mergedRange As Range

For Each Cell In ActiveSheet.Range("A1:Y160")
  If Cell.MergeCells = False Then
    Select Case True
      Case Cell.Interior.ColorIndex = 15
           Cell.Locked = True
      Case Else
           Cell.Locked = False
    End Select
  Else
    Set mergedRange = Cell.MergeArea
    
    Select Case mergedRange.Address(0, 0)
        Case "F6:O6"
            If Range("L4").Value = "Whatever" Then
                mergedRange.Interior.ColorIndex = 15    'adjust the color index
                mergedRange.Locked = True
            Else
                mergedRange.Interior.ColorIndex = 15    'adjust the color index
                mergedRange.Locked = True
            End If
        Case "Q126"
            'same as above
        
        Case Else
            Select Case True
              Case mergedRange.Interior.ColorIndex = 15
                   mergedRange.Locked = True
              Case Else
                   mergedRange.Locked = False
            End Select
  End If
Next

[AerosAtar]

Thanks for the reply (and welcome!).

If possible, I would prefer to avoid doing single-cell/mergearea specifications in the code. The form is quite complex, with a not-insignificant number of questions, and a significant number of those have impacts on other questions within the form. I already have too many absolute-references in some of the other code, which makes updating in when additional questions are inserted a real chore. (I realise that seeing the form would probably help explain all this, but I am still waiting on an answer from my employer).

If there is no other way, I will look at switching all of the conditional formatting into the code, but I was really hoping that wouldn't be necessary...