How to highlight time using Conditional format?

[navinrb]

Can anybody help me to write formula for highlighting time within 40 minutes from current time using Conditional format. Thanks in advance.

 

[Peter_SSs]

What, exactly is in the cells to be considered & where are those cells?
Can you give us a small set of dummy sample data with XL2BB and explain in relation to that?

 

[navinrb]

All cellls from A2:F100 contains time only eg A2 7:35 pm, B2:9:00pm etc. I just want to highlight 40 minutes from current time within those cells time. For eg difference between current time - 7:35 pm should be within 40 minutes. I want to highlight those cells

 

[Peter_SSs]

I have assumed
- that the CF needs to continually update.
- that you want to highlight a cell if it is within 40 minutes (before or after) the current time
- that the sheet in question is named "Times"

If that is so, then I would put the formula =MOD(NOW(),1) in a vacant cell in the 'Times' sheet. I used cell J1.

Select from A2 to F100 and apply the CF formula =ABS($J$1-A2)<=1/36

In the ThisWorkbook module in the vba window

Private Sub Workbook_Open()
  Recalc_Times_Sheet
End Sub

In a standard module in vba

Sub Recalc_Times_Sheet()
  Application.OnTime Now + TimeValue("00:00:10"), "Recalc_Times_Sheet"
  ThisWorkbook.Sheets("Times").Calculate
End Sub

Save, close and reopen the workbook. The current time and the CF should now update every minute.

Test with a copy of your workbook.

 

[navinrb]

I have assumed
- that the CF needs to continually update.
- that you want to highlight a cell if it is within 40 minutes (before or after) the current time
- that the sheet in question is named "Times"

If that is so, then I would put the formula =MOD(NOW(),1) in a vacant cell in the 'Times' sheet. I used cell J1.

Select from A2 to F100 and apply the CF formula =ABS($J$1-A2)<=1/36

In the ThisWorkbook module in the vba window

Private Sub Workbook_Open()
  Recalc_Times_Sheet
End Sub

In a standard module in vba

Sub Recalc_Times_Sheet()
  Application.OnTime Now + TimeValue("00:00:10"), "Recalc_Times_Sheet"
  ThisWorkbook.Sheets("Times").Calculate
End Sub

Save, close and reopen the workbook. The current time and the CF should now update every minute.

Test with a copy of your workbook.

Great .Thank you soo much for your help.?