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.

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.

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.

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.

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.

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.

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.

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.