(Excel) Check if values are the same and if they are, adjusting the adjacent cell

Question

What I'm attempting to do is make a list of dates in one column, and have today's date in a specific cell (=TODAY). If the date in column B matches what's in my specified cell (G12), I want the cell to the direct RIGHT of that dated cell from column B to be adjusted to the same value as what's in another cell (F13).

So if today's date is 5/17, my function or solution would check column B for that date, find the cell that has the same date, and then adjust the value of the cell to the right of that date to whatever would be the same as cell (F13). So if cell (F13) has the value of 56, my spreadsheet would check today's date, find that cell in column B and adjust the cell to the right of it to be the same as F13 (56).

I hope that makes sense. I have some experience in VBA if that's what is needed to accomplish this. I'd like to make it automated as much as possible.

Unknown Unknown · Accepted Answer · 2017-05-17T16:01:48

Use AutoFilter to filter for today's date on column B and if there are visible cells, set column C to the value in F13.

With worksheets("sheet1")
    If .AutoFilterMode Then .AutoFilterMode = False
    With .Cells(1, "A").CurrentRegion
        .AutoFilter Field:=2, Criteria1:=date
        With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
            If CBool(Application.Subtotal(103, .Cells)) Then
                .specialcells(xlcelltypevisible).offset(0,1) = .parent.cells(13, "F").value2
            End If
        End With
    End With
    If .AutoFilterMode Then .AutoFilterMode = False
End With

(Excel) Check if values are the same and if they are, adjusting the adjacent cell

1 Answers