Forum Discussion

Jliersenvlst's avatar
Jliersenvlst
Copper Contributor
Apr 19, 2022
Solved

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 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?

developer
excel
Formulas and Functions
office 365
training
  • HansVogelaar's avatar
    HansVogelaar
    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

8 Replies

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Apr 19, 2022

        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.

Resources