Macro to change text properties

[Josiah]

I want to create a code that will cause the text in a cell to be form formatted a certain way if a number in an adjacent cell is greater than or equal to 2000. I need this to happen automatically behind the scenes.

IE:
Cell B1 = 2000 so cell A1 needs to automatically become bold and a different color
Cell B2 = 1999 so cell A2 has no change (normal text)

I know how to format the text but I'm not sure what the code is to point to the adjacent cell (noobish stuff, I know, but I'm still in the EARLY stages of learning VB and all on my own through trial and error and boards like this).

My document has 12 sheets (one for every month). I need a specific range in each sheet to be setup to be formatted in this way.

Thanks!

[jasoncw]

This can definitely be accomplished rather easily in VBA, but why not just use conditional formatting on the cells? For example, in your example, select cells A1:A20 (or how many you have to format). Format > Conditional Formatting > Formula Is > "=B1>=2000", then select the format.

Jason

[Josiah]

This can definitely be accomplished rather easily in VBA, but why not just use conditional formatting on the cells? For example, in your example, select cells A1:A20 (or how many you have to format). Format > Conditional Formatting > Formula Is > "=B1>=2000", then select the format.

Jason

Wow! I didn't know you could do that in conditional formatting (apply a format to one cell based on another)! Thanks! I'll give that a shot...

[Josiah]

Jason - That's not working how I need it to. Due to the nature of my cells, I'd have to format each cell individually (to point to it's adjacent cell).

How would the macro be accomplished?

[jasoncw]

If there is a contiguous range of cells, and for each of the cells, you are looking at the adjacent cell, then yes, this can be done all at once.

Say for example you are looking to conditional format A1:A10, based on the values of B1:B10. Take these steps:

1. Starting with cell A1, select the range A1:A10 (the active cell should still be A1).
2. Select Format > Conditional Formatting, then select Formula Is, and type:
=B1>=2000 (do not use any $)
3. Select your formatting (bold, different color)
4. Select OK

This will now include the conditional format in all cells for A1:A10, which will change format based on their adjacent cells.

Jason

[Josiah]

If there is a contiguous range of cells, and for each of the cells, you are looking at the adjacent cell, then yes, this can be done all at once.

Say for example you are looking to conditional format A1:A10, based on the values of B1:B10. Take these steps:

1. Starting with cell A1, select the range A1:A10 (the active cell should still be A1).
2. Select Format > Conditional Formatting, then select Formula Is, and type:
=B1>=2000 (do not use any $)
3. Select your formatting (bold, different color)
4. Select OK

This will now include the conditional format in all cells for A1:A10, which will change format based on their adjacent cells.

Jason

You so totally rock! (I've always wanted to say that to someone!)

Thanks for your help! Worked perfectly. Sometimes it's amazing the simple things that get missed in Excel.

[jasoncw]

Glad it helped. If coding can be avoided by a simple format issue, that is the way to go IMO. Use VBA when you cannot do something easily within the Excel UI.

Jason