Is it possible to define a range that moves with the date ?

[James thunderbolt]

Say on column A I have a list of dates, I would like to DIM the cell next to today's date as the range.

So if A1 is 18/05/2016 then the defined range (lets call this TODAYRANGE) should be B1, but, if A2 is 19/05/2016 then the defined range tomorrow should be B2 ?

Is this doable?

Thanks
JT

 

[NARAYANK991]

Hi ,

There is no need to use a named range ; using Conditional Formatting , this can certainly be done.

Suppose the comparison date is in the cell E1 ; select the data range , say A1:B25 , and enter the following CF formula :

=AND($A1 = $E$1,COLUMN() = 2)

Select the appropriate font colour , say light gray.

Based on the date entered in E1 , the corresponding cell in column B will be dimmed.

Narayan

 

[jindon]

Something like

    Dim x, TODAYRANGE As Range
    x = Application.Match(CLng(Date), Columns("a"), 0)
    If IsError(x) Then MsgBox "No match": Exit Sub
    Set TODAYRANGE = Cells(x, 2)
    MsgBox TODAYRANGE.Address(0, 0)

 

[NARAYANK991]

Hi ,

Sorry , didn't notice the section in which you posted your question ; ignore my answer.

Narayan

 

[James thunderbolt]

Thanks for your suggestions - Jindon, worked perfectly this is excellent - thankyou very much

 

[jindon]

You are welcome.