Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
hopefully a basic macro
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
hopefully a basic macro
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
hopefully a basic macro
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) "MelB" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
hopefully a basic macro
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. "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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
hopefully a basic macro
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
hopefully a basic macro
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
hopefully a basic macro
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Basic Macro Help | Excel Discussion (Misc queries) | |||
Basic Macro Query | Excel Discussion (Misc queries) | |||
Basic Macro Knowledge | Excel Discussion (Misc queries) | |||
Very very basic macro help | Excel Discussion (Misc queries) | |||
Visual Basic Macro | Excel Discussion (Misc queries) |