Help with cell formatting in macro.

[Pank]

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.

 

[Per Jessen]

As always, post the code you have got, so we can elaborate on that.

Regards,
Per

 

[Pank]

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]

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]

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]

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]

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]

Thanks for your reply.

I'm glad you made it work as desired.

Best regards,
Per