I am currently using the conditional formatting function in excel for a spreadsheet. I would like to create a macro that will do the same thing as the spreadsheet is getting too large. My current conditional formatting is as follows:
Format only cells that contain blanks: no format set - stop if true (if the cell in column A is blank than do not format. Stop if true)
Format only cells that contain non blanks: no format set - stop if true (if a the cell in column B is not blank than do not format. Stop if true)
Formula: =IF($B$2:$B$2651="",$A$2:$A$2651<=TODAY()) (If a cell in column B is blank than format the cell in column A if the cell in column A meets the following criteria: If the date is today or prior to today highlight cell in colmn A red)
I need a macro to do the same thing:
Do not format if the cell in column A if the cell in column A is blank.
Do not format if the cell in column A if the cell in column B is not blank.
Format column A if: a cell in column B is blank and the date in column A is today or prior to today than highlight the cell in column A red.
Do not format the cell in column A if the date in the cell in column A is later than today.
(there should be no formatting in column B at all no matter what. I just want to format the cell in Column A based on the criteria of the cell in column A and column B).
So, basically I have a spreadsheet that has a forecast date in column A. There may or may not be a date in the cell in column B. Column B is the day the task was actualized.
I want to highlight the forecast date in column A if it is today or past due. If a the task is actualized in column B than there is no need for any formatting.
Thank you for your help on this. I know the macro should be pretty easy but I am not familiar with writing macros so it is a bit difficult for me. My spreadsheet has 2651 rows of data and each row has a column with a forecast and an actual date. There are 565 columns. Lots of data to review. I want past due forecast dates to be highlighted in red so it stands out easily.
Bookmarks