Locking down a date with conditional formatting

[Jliersenvlst]

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 boxes and in D the True and False statement of said checkboxes. 

Now the due date is 10 days after any date that is set in Column A. So for example when in A2 the date is set on 19-04-2022 the expire date would be 29-04-2022. Now I have made formula with A2+10 for the expire date.

Now comes the tricky part for me. When is check the checkbox I want the date when the checkbox is checked to overwrite the expire date. So for example if the expire date is 29-04-2022 and I checked the box on the 27-04-2022 it replaces the 29-04 and turns green. 

Is this possible and if so how?

[#2]

@Jliersenvlst 

Did you use Form controls or ActiveX controls for the check boxes?

[#3]

I used Form Controls

[#4]

@Jliersenvlst 

You can create a macro like this:

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 = "=RC[-1]+10"
    End If
End Sub

and assign it to all of the check boxes.

See the attached sample workbook. It is a macro-enabled workbook, so you'll have to allow macros when you open it.

[#5]

@Hans Vogelaar That almost did it for me, it is something I was looking for thanks. But I have gotten a question 

Now I have checked it see image below

Checked

But when I unchecked it, it returns to the 1900's not the previous date, is there a way to fix that? 

Unchecked and it returns to the 1900's

Nevertheless thank you so much :) 

[#6]

@Jliersenvlst 

In your first post, you wrote that the date in column B is 10 days after the date in the column to the left.

When you clear the check box, the code restores the formula that adds 10 days to the date in the cell to the left.

But in your real setup, the start date is probably not in the cell to the left.

So I'd need to know which column to look at.

[#7]

@Hans Vogelaar 

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.

[#8 · Accepted solution]

@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

[#9]

That did it! thank you so much