Loop

[vidplaylist]

Sales is already sorted with greater number and i want that sales greater than 50 get bold through "do while loop". I write this code but it's not working can someone please help with this.

Sub formatting1()
Dim Sales As Long
Sales = ActiveCell.Value
Do While Sales > 50
ActiveCell.Font.Bold = True
ActiveCell.Offset(1, 0).Activate

Loop
End Sub

Thank you,

 

[Richard Schollar]

Hi

Perhaps:

Sub Test()
Dim r As Range, cell As Range
Set r = Range("A2:A" & Cells(Rows.Count,"A").End(xlUp).Row)  'assuming data in col A - amend as appropriate
For Each cell In r
  If cell.Value> 50 Then cell.Font.Bold = True
Next cell
End Sub

 

[Weaver]

Sub formatting2()
  r = ActiveCell.Row
  c = ActiveCell.Column
  lr = Cells(Rows.Count, c).End(xlUp).Row
  Do While Cells(r, c).Value > 50 Or r > lr
    r = r + 1
  Loop
  Range(ActiveCell, Cells(r, c)).Font.Bold = True
End Sub

 

[vidplaylist]

Richards,

Can you please explain the meaning of this part

("A2:A" & Cells(Rows.Count,"A").End(xlUp).Row)

Thank you,

 

[vidplaylist]

Weaver,

Can you please explain the meaning of this statement

lr = Cells(Rows.Count, c).End(xlUp).Row

Thank you,

 

[Richard Schollar]

Richards,

Can you please explain the meaning of this part

("A2:A" & Cells(Rows.Count,"A").End(xlUp).Row)

Thank you,

Sets the range to be checked (and bolded if value is greater than 50). The Cells(Rows.Count,"A").End(xlUp).Row returns the row of the last line of data by going to the last row in column A and (effectively) pressing Ctrl+Up Arrow to find the last row.

 

[Weaver]

Weaver,

Can you please explain the meaning of this statement

lr = Cells(Rows.Count, c).End(xlUp).Row

Thank you,

See Richard's explanation, it's a slightly different play on the same principle.

Knowing where the data ends can be useful sometimes, as it stops you testing cells after there data runs out.

Overall, Richard's solution might be better in that it doesn't depend on the results to be sorted for it to work correctly.

Alternatively, have you considered using conditional formatting?

 

[zakasnak]

Hi

Perhaps:

Sub Test()
Dim r As Range, cell As Range
Set r = Range("A2:A" & Cells(Rows.Count,"A").End(xlUp).Row)  'assuming data in col A - amend as appropriate
For Each cell In r
  If cell.Value> 50 Then cell.Font.Bold = True
Next cell
End Sub

Richard,

This works for me but I would like to BOLD the row from A:O for each row that the cell in Col D equals "Total"

Help? (THANKS!!)

 

[Richard Schollar]

Hi Carole

Try this:

Sub Test()
Dim r As Range, cell As Range
Set r = Range("A2:A" & Cells(Rows.Count,"A").End(xlUp).Row)  'assuming data in col A - amend as appropriate
For Each cell In r
  If cell.Offset(0,3).Value> 50 Then cell.Resize(,15).Font.Bold = True
Next cell
End Sub

Relevant amendments are in red

 

[zakasnak]

Hi Carole

Try this:

Sub Test()
Dim r As Range, cell As Range
Set r = Range("A2:A" & Cells(Rows.Count,"A").End(xlUp).Row)  'assuming data in col A - amend as appropriate
For Each cell In r
  If cell.Offset(0,3).Value = "Total" Then cell.Resize(,15).Font.Bold = True
Next cell
End Sub

Relevant amendments are in red

PERFECT! Thank you!