I have the following code in a Worksheet_Change() event that locks cells based on their interior colour.

Code:
Dim cell
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 True
      Case mergedRange.Interior.ColorIndex = 15
           mergedRange.Locked = True
      Case Else
           mergedRange.Locked = False
    End Select
  End If
Next
This code works perfectly, locking all cells and mergeareas with a 25% grey background.

However, some cells/mergeareas contain conditional formatting to change the colour of the background. This conditional formatting is formula-based, as the formatting is based on the contents of other cell(s).

For example: mergearea(F6:O6)'s formatting is dependent on the value of cell(L4), while cell(Q126)'s formatting is based on the values of cells(Q138, F126 and B126).

This means that certain cells/mergeareas that should not be locked are being locked (due to having a default 25% grey background), while others that should be locked are not being locked (due to not having a default 25% grey brackgroud).

I've seen the code on Rick's thread (http://www.excelfox.com/forum/f22/ge...ng-or-not-338/), but I am not sure how to incorporate it into the above (or if there is another way of doing what I want). I am also aware of the DisplayFormat property, but unfortunately this is not an option as I need backward-compatability with Excel 2007 (some users are running 2007, while others are using 2010 - I am coding in 2007).

I may be able to upload the form that this code is being used in, but am currently seeking confirmation from my employer that I am permitted to do so.