Solved Excel how to conditional format with a macro

how to write conditional statements in excel

How can I write a macro so that it will conditionally format the cells of three different columns?

I would like to understand how to write the formula so that it runs until it sees a blank row for those columns that I want to condition. I receive a regular report for each of my customers but the number of rows will vary based on the amount of business that particular customer has with us.

Each report has dates that are important in Columns L,M, & N. I want to use the following formulas to conditionally format the columns M&N yellow for dates prior than today, and red for dates after today. I want to use the same highlighting scheme on column L except this column is highlighted if the date is 122 days away from today. If it is more than 122 days from today, it will format in red.

Add a command button to Sheet1 in this workbook (instructions below)

The macro will be run when the button is clicked.

When setup, the process will be to open the workbook that needs the CFs applied.

Then open the ApplyCFs.xlsm workbook

Click the button.

An input box will open, asking for the name of the workbook that needs the CF applied to it.

(If it's not open or the name is wrong, the macro closes after a warning/information message).

Then a second input box opens, asking for the name of the worksheet.

(If the name is not present/entered incorrectly, there is a warning/information box and the worksheet name input box is displayed again. Clicking 'Cancel' on this input box closes the macro).

At this point, with the correct workbook name and worksheet name, the CFs are applied to data in columns L, M & N, starting at Row 8 and going to the end of the rows of data (based on the last row in column L that contains data).

To add the button & associated macro to the ApplyCFs.xlsm workbook, follow these steps:

In worksheet "Sheet1" - From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.

Draw the button on the worksheet

In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Note that Sub Button1_Click() and End

sub will already be present, so don't duplicate them. Option explicit and the Private Const lines go before Sub Button1_Click().

The Sub getclr() to End Sub lines go after the End Sub that goes with Button_Click.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

After selecting any cell on Sheet1, the new command button should now respond to a click and run the macro.

I have included two lines where the conditional format colors are applied:

'set fill colors

'Less is for < formulas

'More is for > formulas

dblFillClrLess = 7204607

dblFillClrMore = 5065193

As the color numbers are not easy to relate to actual colors, there is a small macro that allows you to set a normal fill color in cell A1 and the macro tells you what the color number is. You can then use that number in the CF macro.

Change the color in cell A1 in ApplyCF's.xlsm Sheet1 and then in the Visual Basic window select the macro name Sub getclr() and then click the f5 function key. The color number for cell A1 will be displayed.

An alternative is to change the two color lines to:

dblFillClrLess = RGB(250, 20, 20)

dblFillClrMore = RGB(250, 220, 0)

and select red, green & blue values between 0 and 255.

Note, that there is a line near the end of the macro that sets the date formats in the CF range.

You can either set your preferred date format on this line:

'set all the cells in the CF range to the same date format

rngToCF.Resize(rngToCF.Rows.Count, 3).NumberFormat = "dd-mmm-yy"

Note that this macro does not remove CFs. If you want to re-run it, say on a test workbook, to adjust the colors or the formulas, you will need to select the cells and then use Clear - Clear formats, before running the macro again.

As 'Clear formats' also removes date formats, retaining this line, at least for testing is of value.


Category: Forex

Similar articles: