Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with cell formatting in macro.
I have the following problems and would appreciate some help.
I have created macros to do the work, but unfortunately, it creates absolute references to cells, and I do not know how to change them to relative references. Problem 1 For every row that has €˜On Hand in Column B (i.e. B6, B8, B11, B12), the corresponding cell in column C should be BOLD and RED (i.e. C6, C8, C11, C12) Problem 2 Any row that has the letters €˜EOQ, should be vertically centred, and the corresponding cell in Column L should be set to Wrap Text, with the following text inserted in that cell €˜ RFQ ,Last Ordered mm/yy, at £. Any assistance offered would be appreciated. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with cell formatting in macro.
As always, post the code you have got, so we can elaborate on that.
Regards, Per "Pank" skrev i meddelelsen ... I have the following problems and would appreciate some help. I have created macros to do the work, but unfortunately, it creates absolute references to cells, and I do not know how to change them to relative references. Problem 1 For every row that has €˜On Hand in Column B (i.e. B6, B8, B11, B12), the corresponding cell in column C should be BOLD and RED (i.e. C6, C8, C11, C12) Problem 2 Any row that has the letters €˜EOQ, should be vertically centred, and the corresponding cell in Column L should be set to Wrap Text, with the following text inserted in that cell €˜ RFQ ,Last Ordered mm/yy, at £. Any assistance offered would be appreciated. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with cell formatting in macro.
Per,
Firstly, thank you for taking the time to review this post. The code is as follows, however, please note that the macro does not contain any code for the FIND commands that I used (no idea why these are not in):- Range("C6").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C12").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C15").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("L14").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L11").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L5").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub "Per Jessen" wrote: As always, post the code you have got, so we can elaborate on that. Regards, Per "Pank" skrev i meddelelsen ... I have the following problems and would appreciate some help. I have created macros to do the work, but unfortunately, it creates absolute references to cells, and I do not know how to change them to relative references. Problem 1 For every row that has €˜On Hand in Column B (i.e. B6, B8, B11, B12), the corresponding cell in column C should be BOLD and RED (i.e. C6, C8, C11, C12) Problem 2 Any row that has the letters €˜EOQ, should be vertically centred, and the corresponding cell in Column L should be set to Wrap Text, with the following text inserted in that cell €˜ RFQ ,Last Ordered mm/yy, at £. Any assistance offered would be appreciated. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with cell formatting in macro.
Hi Pank
Try if this is what you want: Sub test() Dim FilterRange As Range Set FilterRange = Range("B1:B" & Range("b" & Rows.Count).End(xlUp).Row) FilterRange.Select Selection.AutoFilter Field:=1, Criteria1:="On Hand" With Selection.Offset(0, 1) .Font.Bold = True .Font.ColorIndex = 3 .AutoFilter End With Selection.AutoFilter Field:=1, Criteria1:="EOQ" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With With Selection.Offset(0, 10) .WrapText = True .Value = "RFQ ,Last Ordered mm/yy, at £" End With Selection.AutoFilter End Sub Regards, Per "Pank" skrev i meddelelsen ... Per, Firstly, thank you for taking the time to review this post. The code is as follows, however, please note that the macro does not contain any code for the FIND commands that I used (no idea why these are not in):- Range("C6").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C12").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C15").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("L14").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L11").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L5").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub "Per Jessen" wrote: As always, post the code you have got, so we can elaborate on that. Regards, Per "Pank" skrev i meddelelsen ... I have the following problems and would appreciate some help. I have created macros to do the work, but unfortunately, it creates absolute references to cells, and I do not know how to change them to relative references. Problem 1 For every row that has €˜On Hand in Column B (i.e. B6, B8, B11, B12), the corresponding cell in column C should be BOLD and RED (i.e. C6, C8, C11, C12) Problem 2 Any row that has the letters €˜EOQ, should be vertically centred, and the corresponding cell in Column L should be set to Wrap Text, with the following text inserted in that cell €˜ RFQ ,Last Ordered mm/yy, at £. Any assistance offered would be appreciated. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with cell formatting in macro.
Per,
Firstly, thank you for provide code to help me. Secondly, I am sorry that I did not respond earlier as I have been busy. Thirdly, having tried you code; I find that it satisfies my first problem but not the second problem. For the second problem that I had identified, I get the following:- The text €˜RFQ ,Last Ordered mm/yy, at £ is only visiable in cell L1. Please note that the text EOQ appears alongside other text. If you are going to find it difficult to find EOQ, then please note that the text €˜RFQ ,Last Ordered mm/yy, at £ is only should be inserted in Column L for the previous row that the text €˜On Hand. "Per Jessen" wrote: Hi Pank Try if this is what you want: Sub test() Dim FilterRange As Range Set FilterRange = Range("B1:B" & Range("b" & Rows.Count).End(xlUp).Row) FilterRange.Select Selection.AutoFilter Field:=1, Criteria1:="On Hand" With Selection.Offset(0, 1) .Font.Bold = True .Font.ColorIndex = 3 .AutoFilter End With Selection.AutoFilter Field:=1, Criteria1:="EOQ" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With With Selection.Offset(0, 10) .WrapText = True .Value = "RFQ ,Last Ordered mm/yy, at £" End With Selection.AutoFilter End Sub Regards, Per "Pank" skrev i meddelelsen ... Per, Firstly, thank you for taking the time to review this post. The code is as follows, however, please note that the macro does not contain any code for the FIND commands that I used (no idea why these are not in):- Range("C6").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C12").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C15").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("L14").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L11").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L5").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub "Per Jessen" wrote: As always, post the code you have got, so we can elaborate on that. Regards, Per "Pank" skrev i meddelelsen ... I have the following problems and would appreciate some help. I have created macros to do the work, but unfortunately, it creates absolute references to cells, and I do not know how to change them to relative references. Problem 1 For every row that has €˜On Hand in Column B (i.e. B6, B8, B11, B12), the corresponding cell in column C should be BOLD and RED (i.e. C6, C8, C11, C12) Problem 2 Any row that has the letters €˜EOQ, should be vertically centred, and the corresponding cell in Column L should be set to Wrap Text, with the following text inserted in that cell €˜ RFQ ,Last Ordered mm/yy, at £. Any assistance offered would be appreciated. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with cell formatting in macro.
Hi Pank
Thanks for your reply. With the new informations, this should do it: Sub test() Dim FilterRange As Range Set FilterRange = Range("B1:B" & Range("b" & Rows.Count).End(xlUp).Row) FilterRange.Select Selection.AutoFilter Field:=1, Criteria1:="On Hand" With Selection.Offset(0, 1) .Font.Bold = True .Font.ColorIndex = 3 .AutoFilter End With For Each c In FilterRange If c.Value Like "*EOQ*" Then c.HorizontalAlignment = xlCenter c.VerticalAlignment = xlCenter With c.Offset(0, 10) .WrapText = True .Value = "RFQ ,Last Ordered mm/yy, at £" End With End If Next End Sub Regards, Per "Pank" skrev i meddelelsen ... Per, Firstly, thank you for provide code to help me. Secondly, I am sorry that I did not respond earlier as I have been busy. Thirdly, having tried you code; I find that it satisfies my first problem but not the second problem. For the second problem that I had identified, I get the following:- The text €˜RFQ ,Last Ordered mm/yy, at £ is only visiable in cell L1. Please note that the text EOQ appears alongside other text. If you are going to find it difficult to find EOQ, then please note that the text €˜RFQ ,Last Ordered mm/yy, at £ is only should be inserted in Column L for the previous row that the text €˜On Hand. "Per Jessen" wrote: Hi Pank Try if this is what you want: Sub test() Dim FilterRange As Range Set FilterRange = Range("B1:B" & Range("b" & Rows.Count).End(xlUp).Row) FilterRange.Select Selection.AutoFilter Field:=1, Criteria1:="On Hand" With Selection.Offset(0, 1) .Font.Bold = True .Font.ColorIndex = 3 .AutoFilter End With Selection.AutoFilter Field:=1, Criteria1:="EOQ" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With With Selection.Offset(0, 10) .WrapText = True .Value = "RFQ ,Last Ordered mm/yy, at £" End With Selection.AutoFilter End Sub Regards, Per "Pank" skrev i meddelelsen ... Per, Firstly, thank you for taking the time to review this post. The code is as follows, however, please note that the macro does not contain any code for the FIND commands that I used (no idea why these are not in):- Range("C6").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C12").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C15").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("L14").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L11").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L5").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub "Per Jessen" wrote: As always, post the code you have got, so we can elaborate on that. Regards, Per "Pank" skrev i meddelelsen ... I have the following problems and would appreciate some help. I have created macros to do the work, but unfortunately, it creates absolute references to cells, and I do not know how to change them to relative references. Problem 1 For every row that has €˜On Hand in Column B (i.e. B6, B8, B11, B12), the corresponding cell in column C should be BOLD and RED (i.e. C6, C8, C11, C12) Problem 2 Any row that has the letters €˜EOQ, should be vertically centred, and the corresponding cell in Column L should be set to Wrap Text, with the following text inserted in that cell €˜ RFQ ,Last Ordered mm/yy, at £. Any assistance offered would be appreciated. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with cell formatting in macro.
Per,
Problem 1 resolved. Problem 2 resolved after I made the following changes:- Inserted the following:- Set FilterRange = Range("A1:A" & Range("a" & Rows.Count).End(xlUp).Row) FilterRange.Select After the first End With, as the data starts in column A for the next filter range. Thank you very much for you time and help. "Per Jessen" wrote: Hi Pank Thanks for your reply. With the new informations, this should do it: Sub test() Dim FilterRange As Range Set FilterRange = Range("B1:B" & Range("b" & Rows.Count).End(xlUp).Row) FilterRange.Select Selection.AutoFilter Field:=1, Criteria1:="On Hand" With Selection.Offset(0, 1) .Font.Bold = True .Font.ColorIndex = 3 .AutoFilter End With For Each c In FilterRange If c.Value Like "*EOQ*" Then c.HorizontalAlignment = xlCenter c.VerticalAlignment = xlCenter With c.Offset(0, 10) .WrapText = True .Value = "RFQ ,Last Ordered mm/yy, at £" End With End If Next End Sub Regards, Per "Pank" skrev i meddelelsen ... Per, Firstly, thank you for provide code to help me. Secondly, I am sorry that I did not respond earlier as I have been busy. Thirdly, having tried you code; I find that it satisfies my first problem but not the second problem. For the second problem that I had identified, I get the following:- The text €˜RFQ ,Last Ordered mm/yy, at £ is only visiable in cell L1. Please note that the text EOQ appears alongside other text. If you are going to find it difficult to find EOQ, then please note that the text €˜RFQ ,Last Ordered mm/yy, at £ is only should be inserted in Column L for the previous row that the text €˜On Hand. "Per Jessen" wrote: Hi Pank Try if this is what you want: Sub test() Dim FilterRange As Range Set FilterRange = Range("B1:B" & Range("b" & Rows.Count).End(xlUp).Row) FilterRange.Select Selection.AutoFilter Field:=1, Criteria1:="On Hand" With Selection.Offset(0, 1) .Font.Bold = True .Font.ColorIndex = 3 .AutoFilter End With Selection.AutoFilter Field:=1, Criteria1:="EOQ" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With With Selection.Offset(0, 10) .WrapText = True .Value = "RFQ ,Last Ordered mm/yy, at £" End With Selection.AutoFilter End Sub Regards, Per "Pank" skrev i meddelelsen ... Per, Firstly, thank you for taking the time to review this post. The code is as follows, however, please note that the macro does not contain any code for the FIND commands that I used (no idea why these are not in):- Range("C6").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C12").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C15").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("L14").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L11").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L5").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub "Per Jessen" wrote: As always, post the code you have got, so we can elaborate on that. Regards, Per "Pank" skrev i meddelelsen ... I have the following problems and would appreciate some help. I have created macros to do the work, but unfortunately, it creates absolute references to cells, and I do not know how to change them to relative references. Problem 1 For every row that has €˜On Hand in Column B (i.e. B6, B8, B11, B12), the corresponding cell in column C should be BOLD and RED (i.e. C6, C8, C11, C12) Problem 2 Any row that has the letters €˜EOQ, should be vertically centred, and the corresponding cell in Column L should be set to Wrap Text, with the following text inserted in that cell €˜ RFQ ,Last Ordered mm/yy, at £. Any assistance offered would be appreciated. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Help with cell formatting in macro.
Thanks for your reply.
I'm glad you made it work as desired. Best regards, Per "Pank" skrev i meddelelsen ... Per, Problem 1 resolved. Problem 2 resolved after I made the following changes:- Inserted the following:- Set FilterRange = Range("A1:A" & Range("a" & Rows.Count).End(xlUp).Row) FilterRange.Select After the first End With, as the data starts in column A for the next filter range. Thank you very much for you time and help. "Per Jessen" wrote: Hi Pank Thanks for your reply. With the new informations, this should do it: Sub test() Dim FilterRange As Range Set FilterRange = Range("B1:B" & Range("b" & Rows.Count).End(xlUp).Row) FilterRange.Select Selection.AutoFilter Field:=1, Criteria1:="On Hand" With Selection.Offset(0, 1) .Font.Bold = True .Font.ColorIndex = 3 .AutoFilter End With For Each c In FilterRange If c.Value Like "*EOQ*" Then c.HorizontalAlignment = xlCenter c.VerticalAlignment = xlCenter With c.Offset(0, 10) .WrapText = True .Value = "RFQ ,Last Ordered mm/yy, at £" End With End If Next End Sub Regards, Per "Pank" skrev i meddelelsen ... Per, Firstly, thank you for provide code to help me. Secondly, I am sorry that I did not respond earlier as I have been busy. Thirdly, having tried you code; I find that it satisfies my first problem but not the second problem. For the second problem that I had identified, I get the following:- The text €˜RFQ ,Last Ordered mm/yy, at £ is only visiable in cell L1. Please note that the text EOQ appears alongside other text. If you are going to find it difficult to find EOQ, then please note that the text €˜RFQ ,Last Ordered mm/yy, at £ is only should be inserted in Column L for the previous row that the text €˜On Hand. "Per Jessen" wrote: Hi Pank Try if this is what you want: Sub test() Dim FilterRange As Range Set FilterRange = Range("B1:B" & Range("b" & Rows.Count).End(xlUp).Row) FilterRange.Select Selection.AutoFilter Field:=1, Criteria1:="On Hand" With Selection.Offset(0, 1) .Font.Bold = True .Font.ColorIndex = 3 .AutoFilter End With Selection.AutoFilter Field:=1, Criteria1:="EOQ" With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With With Selection.Offset(0, 10) .WrapText = True .Value = "RFQ ,Last Ordered mm/yy, at £" End With Selection.AutoFilter End Sub Regards, Per "Pank" skrev i meddelelsen ... Per, Firstly, thank you for taking the time to review this post. The code is as follows, however, please note that the macro does not contain any code for the FIND commands that I used (no idea why these are not in):- Range("C6").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C12").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("C15").Select Selection.Font.ColorIndex = 3 Selection.Font.Bold = True Range("L14").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L11").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Range("L5").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub "Per Jessen" wrote: As always, post the code you have got, so we can elaborate on that. Regards, Per "Pank" skrev i meddelelsen ... I have the following problems and would appreciate some help. I have created macros to do the work, but unfortunately, it creates absolute references to cells, and I do not know how to change them to relative references. Problem 1 For every row that has €˜On Hand in Column B (i.e. B6, B8, B11, B12), the corresponding cell in column C should be BOLD and RED (i.e. C6, C8, C11, C12) Problem 2 Any row that has the letters €˜EOQ, should be vertically centred, and the corresponding cell in Column L should be set to Wrap Text, with the following text inserted in that cell €˜ RFQ ,Last Ordered mm/yy, at £. Any assistance offered would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions | |||
Cell Formatting w/following Macro | Excel Discussion (Misc queries) |