Macro to control Conditional Formatting

[OLLY-7]

Hi all,

I have a conditional format that I would like to run as a macro instead - really just so I can get a thick red line instead of a thin line as seams to be the only offering in the conditional formatting format window. [the macro puts a red cell border line on the top of a cell if the date is different to the one above]

The macro I have got which is currently not working is as bellow. Can someone very kindly tell me how to change this so will work? Ideally it will not require hitting 'run' every time in order to update the conditional formatting on the sheet.

Please Login or Register  to view this content.

Thank you very much indeed.

Oliver

[Richard Buttrey]

As you identify the thick border is not available in CF

Have you considered setting all borders for any range in question to a thick border and then using CF to identify cells which should NOT have a border and using CF to turn them off.

In essence the reverse of what you've attempted.

[OLLY-7]

Ha!

Thank you Richard.

No I had not thought of it that way round.

That works perfectly and no macro required!

Thanks very much indeed.


Oliver

[jeffreybrown]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Richard Buttrey]

Ha!

Thank you Richard.

...snipped

Oliver

My pleasure and thanks for the Rep.

[OLLY-7]

Thank you Jeff.

Will do.

However I just realised that while I can reverse the conditional formatting to do what I want (I.e. make all top and bottom cell borders in the table thick red lines and then use conditional formatting to turn the top cell border line to a thin black line if cell above is the same (ie. dates are the same)....

...What is happening of course is that when I add rows (as will happen) in the middle of the table, the conditional formatting is getting split up leading to undesirable results.

Also adding data rows at the bottom of the table does also not extend the formatting range.

so still have an issue.

Might a macro still be the answer?

I would like this to be automated as can not rely on the user being an excel wiz and fixing the formatting range every time they add rows.


I attach the worksheet which should show the issue.


If someone has a solution I'll be most obliged.


Oliver

[OLLY-7]

Might it be possible to have NO conditional formatting in the table, have all table top and bottom border lines as thin black lines and then use a macro with some kind of IF statement to format top borders with a thick red line if the cell above (in date column) is different... i.e.
This all assumes / hopes that the range in the macro will grow when rows are added.

So something like...

IF: =$AN7<>$AN6

Then do this for each cell in complaint row:

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Color = -16776961
.TintAndShade = 0
.Weight = xlThick

Do this formatting to this range: =$B$7:$BA$29


(The above section of code is from a macro I recorder - so wondering if this can be incorporated into an IF function rather than trying to code conditional formatting which, as we know, does not allow the thick red line option (weird), but clearly thick red line formatting CAN be coded!



Thank you again.