Macro to Format a Row Based on IF Statement

[Michael151]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> Hello all,

Just need a little help writing a macro that will format a row based on an If Greater Than statement.

In each row, if the date in the column marked “Effective Date” (row 1 header) comes after (is greater than) the date in the column marked “Start Date” (row 1 header) in that row, then the entire row font is changed to red and made bold.

Help is most appreciated – thank you!

 

[Andrew Poulsom]

Can't you use conditional formatting for that?

 

[Michael151]

Possibly, but the columns containing the Effective Date and Start Date are not always in the same column letters - I'd like to use the header in row 1 to identify. How would I incorporate the conditional formatting into a macro?

 

[Andrew Poulsom]

Select all the rows you want to format and try this Conditional Formatting formula:

=INDEX(2:2,MATCH("Effective Date",$1:$1,FALSE))>INDEX(2:2,MATCH("Start Date",$1:$1,FALSE))

The formula assumes that the active cell is in row 2 when you enter it. Adjust to suit.

 

[Michael151]

Hi Andrew,

Thanks for that but unfortunately it did not work. Would there be any way to write this as a macro using VBA?

Something like: find "Effective Date" and "Start Date" in row 1 header.
IF Effective Date is greater than Start Date, then make entire row bold, color index 3

I appreciate your help!

 

[Andrew Poulsom]

It worked for me. In what way didn't it work for you? What do these formula return when entered in a couple of spare cells on your worksheet?

MATCH("Effective Date",$1:$1,FALSE)
MATCH("Start Date",$1:$1,FALSE)

 

[Michael151]

Thanks - I actually just figured it out using VBA. Thanks for your help though!