hopefully a basic macro

[Guest]

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.

 

[Guest]

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

 

[Bob Phillips]

For Each cell In ActiveSheet.UsedRange
If cell.Value = "N/A" Then
If cell.Column <> 1 Then
cell.Copy
cell.Offset(0, -1).PasteSpecial Paste:=xlPasteFormats
End If
End If
Next cell


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I successfully copied the macro in and it is doing someing as it goes to the
end of the page, but it is not changing the formatting of the cells with
"N/A" to match the cell adjacent and to the left of it. I did correct "#N/A"
to "N/A" in the code.

Any suggestions?

Also, when this happens is this going to delete any conditional formatting I
have on the cell?

Many thanks.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.