Apr 19 2022 05:44 AM
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?
Apr 19 2022 06:46 AM
Did you use Form controls or ActiveX controls for the check boxes?
Apr 19 2022 07:07 AM
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.
Apr 19 2022 10:26 PM
@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
But when I unchecked it, it returns to the 1900's not the previous date, is there a way to fix that?
Nevertheless thank you so much :)
Apr 20 2022 01:05 AM
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.
Apr 20 2022 02:23 AM
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.
Apr 20 2022 02:34 AM
SolutionThanks. 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
Apr 20 2022 06:50 AM
Apr 20 2022 02:34 AM
SolutionThanks. 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