Locking down a date with conditional formatting

Hello, I am not very familiar with conditional formatting but the situation is as follows: In column A I have gotten dates in Colomn B I have the expire dates and in Column C i have Check b...

developer

excel

Formulas and Functions

Apr 20, 2022

Thanks. Since the formula in column O refers to column J instead of to the column immediately to the left, we have to change the formula:

Sub CheckBox_Click()
    Dim nme As String
    Dim shp As Shape
    Dim rng As Range
    nme = Application.Caller
    Set shp = ActiveSheet.Shapes(nme)
    Set rng = shp.TopLeftCell
    If shp.ControlFormat.Value = 1 Then
        rng.Offset(0, -1).Value = Date
    Else
        rng.Offset(0, -1).FormulaR1C1 = "=RC10+10"
    End If
End Sub

Jliersenvlst

Copper Contributor

Apr 20, 2022

HansVogelaar

This is the setup. The one with the macro is in Column P and the formula in Column O is simple: =J4+10 within a conditinal rule.

Hope this helps.

HansVogelaar

MVP

Apr 20, 2022

Jliersenvlst

Thanks. Since the formula in column O refers to column J instead of to the column immediately to the left, we have to change the formula:

Sub CheckBox_Click()
    Dim nme As String
    Dim shp As Shape
    Dim rng As Range
    nme = Application.Caller
    Set shp = ActiveSheet.Shapes(nme)
    Set rng = shp.TopLeftCell
    If shp.ControlFormat.Value = 1 Then
        rng.Offset(0, -1).Value = Date
    Else
        rng.Offset(0, -1).FormulaR1C1 = "=RC10+10"
    End If
End Sub

Jliersenvlst
Copper Contributor
Apr 20, 2022
That did it! thank you so much

Forum Discussion

Locking down a date with conditional formatting

Resources