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

[kennyb21167]

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.

[#2]

@kennyb21167 

Range("D2").Select

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.

[#3]

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.

[#4]

@kennyb21167 

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.

[#5]

@OliverScheurich 

Sorry for the delayed reply. So I'm not quite sure where the error is occurring but I've attached a screenshot of what is happening and so you can see that only half of the macro is functioning on my end.

I have also reattached your example document after I have run/saved the macro and as you can see in worksheet 2 the macro did not run correctly.

[#6]

@kennyb21167 

Sorry for the delayed reply. Unfortunately i can't imagine why the code doesn't work properly on your PC especially as i work with Excel 2013 as well and i assume that we both work with Windows10. There have been posts concerning Runtime error 5 in the Microsoft Tech Community. Maybe you can find a reason for the invalid procedure call or argument error or you want to post a question regarding this error message.

Alternatively you could record the macro yourself. This might be the easiest way to solve this error.