How to highlight time using Conditional format?

navinrb

Board Regular
Joined
Jun 9, 2020
Messages
63
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
Can anybody help me to write formula for highlighting time within 40 minutes from current time using Conditional format. Thanks in advance.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 9, 2020
Messages
63
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
Private Sub Workbook_Open()
  Recalc_Times_Sheet
End Sub

In a standard module in vba
VBA Code:
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

Board Regular
Joined
Jun 9, 2020
Messages
63
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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
VBA Code:
Private Sub Workbook_Open()
  Recalc_Times_Sheet
End Sub

In a standard module in vba
VBA Code:
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.💟
 

Watch MrExcel Video

Forum statistics

Threads
1,123,231
Messages
5,600,427
Members
414,384
Latest member
joehalks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top