• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to add columns to a macro

Eloise T

Active Member
The following VBA segment (in the 2nd line of code) changes the TV Model screen size between 70 and 90 inches to red and Bold and starts in Row 5, Column D. I need to also include Columns H, L, P, and T.
Thank you in advance.

Code:
Sub REDnBOLD()
  For Each cll In Range(Cells(5, "D"), Cells(Rows.Count, "D").End(xlUp)).Cells

  With cll
  x = Evaluate("MIN(IFERROR(FIND(ROW(10:99)," & .Address(0, 0, , 1) & "),""""))")
  If x > 0 Then
  y = CLng(Mid(cll.Value, x, 2))
  If y >= 70 And y <= 90 Then
  With .Characters(Start:=x, Length:=2).Font
  .FontStyle = "Bold"
  .Color = vbRed  'Red  -16776961  vbRed  RGB(255, 0, 0)
  End With
  End If
  End If
  End With
  Next cll
  Beep
End Sub
 
Not that you'd need to see this, but here's a part of the file. The macro affects the red highlighted cells. "70" The macro works but will only do one column at a time and I need it to work on multiple columns.

Thank you again for looking.

upload_2019-1-15_17-15-25.png
 
Code:
Sub REDnBOLD()
Dim i As Long
For i = 4 To 18 Step 4
  For Each cll In Range(Cells(5, i), Cells(Rows.Count, i).End(xlUp)).Cells

  With cll
  x = Evaluate("MIN(IFERROR(FIND(ROW(10:99)," & .Address(0, 0, , 1) & "),""""))")
  If x > 0 Then
  y = CLng(Mid(cll.Value, x, 2))
  If y >= 70 And y <= 90 Then
  With .Characters(Start:=x, Length:=2).Font
  .FontStyle = "Bold"
  .Color = vbRed  'Red  -16776961  vbRed  RGB(255, 0, 0)
End With
  End If
  End If
  End With
  Next cll
  Next i
  Beep
End Sub
 
Code:
Sub REDnBOLD()
Dim i As Long
For i = 4 To 18 Step 4
  For Each cll In Range(Cells(5, i), Cells(Rows.Count, i).End(xlUp)).Cells

  With cll
  x = Evaluate("MIN(IFERROR(FIND(ROW(10:99)," & .Address(0, 0, , 1) & "),""""))")
  If x > 0 Then
  y = CLng(Mid(cll.Value, x, 2))
  If y >= 70 And y <= 90 Then
  With .Characters(Start:=x, Length:=2).Font
  .FontStyle = "Bold"
  .Color = vbRed  'Red  -16776961  vbRed  RGB(255, 0, 0)
End With
  End If
  End If
  End With
  Next cll
  Next i
  Beep
End Sub
*T*H*A*N*K* *Y*O*U* !!!

AND, after pouring over it for a moment, I actually understand what you did even though my understanding of VBA will fit comfortably on the head of a needle.
 
Back
Top