Macro for Conditional Formatting - Excel 2007 based on Date/Time Value

    I have a worksheet whose information changes constantly. We run the report weekly to create KPI's. I need to highlight the cells in the Arrival Column for everything received from 4 pm onward. I was given the formula on another post by NBVC to add to the conditional formatting -

    Code
    MOD(A2,1)>TIME(16,0,0)

    - which works great. However, when I replace the data in my Master Worksheet, the conditional formatting formula changes and I have to keep manually changing it.


    I am preparing the worksheet to be worked by other individuals who are not familiar with Excel, so I want to create a button with a macro that will do everything that needs to be done to get the spreadsheet ready. I am attaching a sample worksheet of what I am looking for. I already have a macro that Fixes the dates as the system downloads it with a leading space. I now want to create a second macro (or edit the existing macro - Fix Dates) that will go to the Main Sheet containing the data and will highlight in yellow all the Cells that contain Time that equals or is greater than 4. I have looked at other threats but cannot figure out how to apply it to my scenario.


    Note that once a week, I have to delete the information on the Main Sheet and replace it with the current information. I never know the number of rows that I am going to have for any given week, but my columns are consistent.


    Any suggestions or help would be much appreciated. Thank you - Monti

    Re: Macro for Conditional Formatting - Excel 2007 based on Date/Time Value


    I will look at your sample workbook, but right now I have a question:


    Is it necessary to use Conditional Formatting? Does the background color change during the week?

    Re: Macro for Conditional Formatting - Excel 2007 based on Date/Time Value


    Hello - thanks for your help - no it does not have to be conditional formatting. Since the data changes every week, the entire sheet is replaced weekly with the new information. I need a way to highlight the cells that shows Arrival equal to or greater than 4 pm in any way possible. Conditional Formatting is just something that I am familiar with.


    Thanks again- Monti

    Re: Macro for Conditional Formatting - Excel 2007 based on Date/Time Value


    If you remove the current conditional formatting, then select all of column B and apply format formula:


    =MOD($B1,1)>=TIME(15,59,0)


    this should work.... make sure the applies to range says $B:$B


    You don't need to change $B1 in the formula to a range, the conditional format feature will automatically change the B1 to B2 in B2, etc....

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Macro for Conditional Formatting - Excel 2007 based on Date/Time Value


    Thanks NBVC - I was able to insert the formula you gave me into a macro which is working perfectly now. I appreciate your help. Monti

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login