Turn on cell lock if conditional format rule is true

[General Ledger]

Dear All,

I am trying to create a macro that will set the Lock check box to True for a cell if Conditional Formatting rule #1 for that cell is True.

I borrowed some code and ended up with the following that does not work correctly. This code never results in a True.

I don't understand the purpose of the r1c1Formula or the abFormula.

Sub LockCellIfConditionalFormatTrue()
Dim cell As Range
On Error Resume Next
For Each cell In ActiveSheet.Range("a1:a3")
With cell
    r1c1Formula = Application.ConvertFormula(Formula:=.FormatConditions(1).Formula1, _
            fromreferencestyle:=Application.ReferenceStyle, _
            toreferencestyle:=xlR1C1)
    abFormula = Application.ConvertFormula(Formula:=r1c1Formula, _
            fromreferencestyle:=xlR1C1, _
            toreferencestyle:=Application.ReferenceStyle, _
            toabsolute:=True, relativeto:=.Cells)
 
    If Evaluate(abFormula) = False Then
        Locked = False
    Else
        Locked = True
    End If
End With
Next cell
End Sub

Any help?

Thanks,

GL

 

[adam087]

If the conditional formating condition is fairly simple I might be tempted to just test directly. This code should catch each entry as it is made.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim ValRange As Range
 
Select Case Target.Column 'limit the check to certain columns...
Case 1, 2
    'do nothing
Case Else
    Exit Sub
End Select
Select Case Target.Row '...and rows
Case 1 - 500
    'do nothing
Case Else
    Exit Sub
End Select
If Target.Value > 5 Then 'whatever your condition is
    Target.Locked = True
Else
    Target.Locked = False
End If
End Sub

Regards
Adam

 

[Jaafar Tribak]

Which CF do you have ? Operators or Formula ?

 

[General Ledger]

adamo87,

The situation is complicated in that there are several conditional format rules. There are different rules for different cells. That is why I do not want to test for the indivudual rules in the macro.


Jaafar Tribak,

I am using Formula based conditions.


I am trying to test: If the formula for Conditional Format rule #1 results in "True", then turn on the Lock property of that cell.

Thanks,

GL

 

[Jaafar Tribak]

This should do it :

Sub LockCellIfConditionalFormatTrue()

    Dim cell As Range
    
    On Error Resume Next
    For Each cell In ActiveSheet.Range("a1:a3")
        cell.Locked = Evaluate(cell.FormatConditions(1).Formula1)
    Next cell
    
End Sub