Font name: Arial
Size: 8
Not underlined
Not italicized
Not Bold
Text is centered
No indentation

So I just need font as specified above and the fill color copied over.
Though both cells have borders (different types) I don't want this changed or
copied from the adjacent cell.

Hope that makes sense and is not too complicated. I am also going to start
reading up on macros but I kind of need a quick fix on this one since it is
due soon.

Thanks.


"Gary''s Student" wrote:

At first it seems simple to copy some of the aspects of format without
PasteSpecial.

However, without PasteSpecial, we need to determine and carry over:

1. the name of the font
2. the size of the font
3. whether the font is underlined
4. whether the cell it italisized
5. justify right or left
6. any indentation
7. etc.

It can be done if we specifically limit the characteristics.
--
Gary''s Student - gsnu200757


"MelB" wrote:

My apologies- it actually is working. The only problem is that it is copying
the border formatting as well as the font (color and bold). I don't want the
border formatting changed in the cell with "N/A". What needs to be changed
in the code.

Many thanks for the help.

"Gary''s Student" wrote:

I assume you mean #N/A

Sub color_me_elmo()
Set r = Intersect(ActiveSheet.UsedRange, Range("b1:iv65536"))
For Each rr In r
If rr.Text = "#N/A" Then
rr.Offset(0, -1).Copy
rr.PasteSpecial Paste:=xlPasteFormats
End If
Next
End Sub

If you really mean N/A
then fix the code.


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Gary''s Student - gsnu200757


"MelB" wrote:

I don't have experience with macros but am hoping that this is a basic enough
one for me to get my feet wet with.

I would like a macro to do the following:

Wherever the word "N/A" appears in a worksheet I would like the macro to
copy the background fill and font color/type from the cell directly adjacent
and to the left of it.

For example if "N/A" appears in cell A2, I want the background color and
font properties from cell A1 copied into cell A2.

I have conditional formatting set up in all of the cells that will
eventually contain "N/A", but need all three available conditional
formattings for other things and since this is something that is consistent
throughout the worksheet I was hoping I could take care of this piece with a
macro.