Using a macro to apply two different conditional formatting functions to same table

New Contributor

Hello, I am trying to create a macro that will format my data table to apply two different conditional formats to different columns. When recording the macro everything works the way I anticipate. However, when I then apply that macro to another set of data on a new document only the first of the conditional formatting processes I've recorded works. I noticed that when reviewing the rules applied by the macro that the "Applies to" section for the first set of rules for those columns has changed from =$A$2:$D$669 when recorded to =$E$2 when applied through the macro. Is there a way to prevent this from happening so that my macro will apply the original conditional formatting the way I intended when recorded?


Here are the two conditions that I am trying to apply to the table (columns set in the following manner: A=Item, B=Group, C=Min Quantity, D=Quantity, and E=Expiration Date):


1. =$D2<$C2 it will change the font color of the text from column A-D to Red.


2. Cell Value < =TODAY() highlights cell Red and Cell Value between =TODAY() and =TODAY()+90 highlights cell Yellow


Notes: I've recorded the macro in several different ways/orders of operations without it working as well. I tried reversing the order of when the conditional formatting (CF) is applied, I tried applying it before/after formatting the data into a Table, I've tried separating the data into two separate tables and applying the CF, I tried recording two separate macros for each CF and after all these attempts no success, well when recording it seems to always work fine but then after running it on a different document it never seems to work.


Thank you to anyone that may be able to help.

3 Replies




I think this line of code could be missing in your macro.


In the attached file you can run the code with and without this line to return the different results.


Without this line of code i have to manually select cell D2 before running the macro in order to have it working properly.

So, after downloading, opening and running the attached macro I am receiving an error message from Microsoft Visual Basic, "Run=time error '5': Invalid procedure call or argument with the option to End, Debug or Help. I am running Excel 2013 if that makes any difference.

I see that the previewed document appears like what I am after, but again after running the macro it only applied formatting to Columns A-D and not E.


That's surprising because i am running Excel 2013 as well and the macro works as intended in my spreadsheet as you can see in the attached file. In which line of the code does the error appear in your sheet and can you attach your file without sensitive data? This could be helpful to determine why it's not working in your spreadsheet.