Need Macro for formatting

[hilltb]

Hello,

I am new to this forum, and I am hoping someone here can help me. I am using Excel 2003, and I can't get it to do exactly what I want using a macro.

I have an excel document that changes over time, and I want to use a macro to update the formatting as the document changes.

For example, if the value of the first to characters in B2 is not the same as the value of the first two characters in B3, I want to underline cells A2, 2, C2, D2, and E2.

I tried using conditional formatting to do this, and I was able to make it underline B2 only, or even underline all of row 2, but I only want it to apply this formatting to the specified cells, and only when the macro is run.

Here is the logic that I think will make this macro work, but I'm not sure how to program it in excel:
--------------
Start

Remove all current underlining in document

x = 2

IF (column B at row x is NOT blank)

While (Value of first two characters in column B at row x is NOT EQUAL to the Value of first two characters in column B at row( x + 1))

Underline Cell A at row x
Underline Cell B at row x
Underline Cell C at row x
Underline Cell D at row x
Underline Cell E at row x
X = X +1

End
------------------------
A sample of what this document looks like:

\1

A sampe of the desired output.
\1

I have been doing this process by hand every day and it is time-consuming and inefficient. Thank you for your help!

[BigBas]

Hilltb,

I've actually done this with conditional formatting, which may actually run more efficiently, occurs realtime, and wouldn't require that macros be enabled.

First, highlight the entire range upon which you want to incldue the formatting. Then, choose TOOLS - CONDITIONAL FORMAT. In the dropdown, box, choose FORMULA IS, then enter: =$B2<>$B3 This assumed that data starts in cell B1. If there is a header, adjust accordingly. Then, click the Format button, and choose the format to apply. Within the format settings, the second tab, called BORDER, allows you to include a bottom border to cells that meet the criteria. Add a bottom border, and it should work.

Report back if this worked for you.

[hilltb]

Hilltb,

I've actually done this with conditional formatting, which may actually run more efficiently, occurs realtime, and wouldn't require that macros be enabled.

First, highlight the entire range upon which you want to incldue the formatting. Then, choose TOOLS - CONDITIONAL FORMAT. In the dropdown, box, choose FORMULA IS, then enter: =$B2<>$B3 This assumed that data starts in cell B1. If there is a header, adjust accordingly. Then, click the Format button, and choose the format to apply. Within the format settings, the second tab, called BORDER, allows you to include a bottom border to cells that meet the criteria. Add a bottom border, and it should work.

Report back if this worked for you.

Thank you for your reply What you have described works to apply that formatting to the single cell in question, but I also want to apply it to the other cells on that same row.

What I want is: if $B2<>$B3, apply bottom border to $A2, $B2, $C2, $D2, and $E2

Does this make sense?

[BigBas]

If you highlight the entire range upon which you want to apply the formatting, it will include a bottom border to the corresponding columns. The '$' tells the formula to always compare column B.

[hilltb]

Thank you for the reply. Your technique does appear to work. I will need to work on it a bit more to make sure.

Is it possible to do this using a macro instead of using conditional formatting?

[hilltb]

Well, I finally figured out how to do it myself. For my purposes, I really needed to not use conditional formatting.


First I created a helper column, and then I created a loop.

Please Login or Register  to view this content.