MACRO to search for specific text and format entire row

Shree_Finance18 · Jun 18, 2020

Hello everyone - I am a new member and I have found a lot of useful tips on this forum...so a HUGE THANK YOU to everyone!!!!!

I am seeking some assistance on how to update a current MACRO. Right now, it is finding specific text (Column A) and adding Top and Bottom Borders. I actually need to perform this formatting on Columns A:E and G:J - but if that is too much I can do the entire row and remove Column F formatting later.

I know it has to do with the Conditional Formatting that I added, but I am unclear how to modify.

VBA Code:

Range("A:A").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="*Consolidated", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False


Selection.FormatConditions.Add Type:=xlTextString, String:="*Total", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With


End Sub

DanteAmor · Jun 18, 2020

Hi and welcome to MrExcel

Try this:

VBA Code:

Sub test_FC()
  Application.ScreenUpdating = False
  Cells.FormatConditions.Delete
  Range("A:E, G:J").Select
  With Selection
    .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(""Consolidated"",$A1)),ISNUMBER(SEARCH(""Total"",$A1)))", _
      TextOperator:=xlContains
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Borders(xlTop)
      .LineStyle = xlContinuous
      .TintAndShade = 0
      .Weight = xlThin
    End With
    With .FormatConditions(1).Borders(xlBottom)
      .LineStyle = xlContinuous
      .TintAndShade = 0
      .Weight = xlThin
    End With
  End With
  Range("A1").Select
  Application.ScreenUpdating = False
End Sub

Shree_Finance18 · Jun 18, 2020

DanteAmor!!! Thank you so much - this works perfectly!! I should have posted my problem weeks ago. You've saved me a lot of work!!!

DanteAmor · Jun 18, 2020

I'm glad to help you. Thanks for the feedback.

Shree_Finance18 · Jun 19, 2020

Hi DanteAmor,

My fault, I think I should have included the entire MACRO. When I ran just the part you provided it worked perfectly. Now that I am including it in the full MACRO...it is not working as expected. Please advise if you are able to assist. You will see I also attempted to copy your addition to add the Bold font. When I did that, it negated the Top and Bottom thin Border. Also, when I inserted your correction into the full MACRO, the Top and Bottom Borders were continuous, instead of skipping Column F.

VBA Code:

'Changes Date Format

    Range("A12:J12").Select
ActiveCell.FormulaR1C1 = "=MID(R[-10]C[1],8,3)&"" - ""&LEFT(R[-10]C[1],4)"
Range("A12:J12").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
  

'Merge and Center MAIN Report Headers

Range("A10:J10,A11:J11,A12:J12,A13:A13,B15:E15,G15:J15").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.Merge
  

'Changes Row Height and custom BLUE

With Selection.RowHeight
Rows("7:9").Select
Selection.RowHeight = 8
End With

Range("A8:J8").Select

With Selection.Interior
.Pattern = xlSolid
.PatternColor = 1557586
.Color = 10232576
.TintAndShade = 0
.PatternTintAndShade = 0

End With


'Changes Columns Width

Columns("A:A").ColumnWidth = 48
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 11
Columns("D:D").ColumnWidth = 11
Columns("E:E").ColumnWidth = 11
Columns("F:F").ColumnWidth = 1
Columns("G:G").ColumnWidth = 11
Columns("H:H").ColumnWidth = 11
Columns("I:I").ColumnWidth = 11
Columns("J:J").ColumnWidth = 11
  

'Changes from Workday color to Custom ORANGE

Range("A15:E16,G15:J15,G16,H16,I16,J16").Select

With Selection.Interior
.Pattern = xlSolid
.PatternColor = 15575861
.Color = 150780
.TintAndShade = 0
.PatternTintAndShade = 0
End With

'Wraps Text for Headers

Range("A16:J16").Select

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With


'Adds Top and Bottom Thin Borders

Application.ScreenUpdating = False
Cells.FormatConditions.Delete
Range("A:E,G:J").Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(""Consolidated"",$A1)),ISNUMBER(SEARCH(""Total"",$A1)))", _
TextOperator:=xlContains
.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
With .FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
End With
Range("A1").Select
Application.ScreenUpdating = False


Application.ScreenUpdating = False
Cells.FormatConditions.Delete
Range("A:E,G:J").Select
With Selection
.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISNUMBER(SEARCH(""Consolidated"",$A1)),ISNUMBER(SEARCH(""Total"",$A1)))", _
TextOperator:=xlContains
.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Font
.Bold = True
End With
End With
Range("A1").Select
Application.ScreenUpdating = False

'Adds THICK Border to Section Headers

Range("A15:A16,B15:E16,G15:J16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Color = -16777216
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Color = -16777216
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Color = -16777216
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Color = -16777216
.TintAndShade = 0
.Weight = xlMedium
    End With

'Adds THICK Borders on each large section

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A17:A78").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B17:E78").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("G17:J78").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("G17:J78").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With


'Adds 10 spaces on LAST Total

Range("A78").Select
ActiveCell.FormulaR1C1 = " Total: Adventist HealthCare, Inc."


'Adds THICK Border to Individual Header Text

Range("B16,C16,D16,E16,G16,H16,I16,J16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With


'Page Layout and Delete Header Rows

    Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B19:E19,G19:J19,B78:E78,G78:J78").Select
Range("B19:E19,G19:J19,B78:E78,G78:J78").Activate
Selection.NumberFormat = "$ #,##0,;$ (#,##0,)"
Rows("1:3").Select
Selection.Delete Shift:=xlUp
Range("B14").Select
ActiveWindow.FreezePanes = True
Range("A1:J75").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$75"
Application.PrintCommunication = False

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With

Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$75"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With

'Header Font Style, Size, and Date

Range("A1").Select
With Selection.Font
.FontStyle = "Bold"
.Size = 14
    End With

Range("A2:A3, A7:A10").Select
With Selection.Font
.FontStyle = "Bold"
.Size = 12
    End With

Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=R[6]C"
    Range("A4").Select

End Sub

DanteAmor · Jun 19, 2020

You have to put the conditional format before you combine the cells.
I allowed myself to review all your code and optimize it:

VBA Code:

Sub Shree()
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  'Clean sheet
  Cells.UnMerge
  Cells.ClearFormats
  Cells.FormatConditions.Delete
  
  'Adds Top and Bottom Thin Borders
  Range("A:E, G:J").Select
  With Selection
    .FormatConditions.Add Type:=xlExpression, _
      Formula1:="=OR(ISNUMBER(SEARCH(""Consolidated"",$A1))," & _
      "ISNUMBER(SEARCH(""Total"",$A1)))", TextOperator:=xlContains
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Borders(xlTop)
      .LineStyle = xlContinuous
      .TintAndShade = 0
      .Weight = xlThin
    End With
    With .FormatConditions(1).Borders(xlBottom)
      .LineStyle = xlContinuous
      .TintAndShade = 0
      .Weight = xlThin
    End With
    .FormatConditions(1).Font.Bold = True
  End With
  
  Range("A12") = Mid(Range("B2"), 8, 3) & " - " & Left(Range("B2"), 4)

  'Merge and Center MAIN Report Headers
  With Range("A10:J10,A11:J11,A12:J12,A13:A13,B15:E15,G15:J15")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    .Merge
  End With
  
  'Changes Row Height and custom BLUE
  Rows("7:9").RowHeight = 8
  With Range("A8:J8").Interior
    .Pattern = xlSolid
    .PatternColor = 1557586
    .Color = 10232576
    .TintAndShade = 0
    .PatternTintAndShade = 0
  End With
  
  'Changes Columns Width
  Columns("A:A").ColumnWidth = 48
  Columns("B:E").ColumnWidth = 11
  Columns("F:F").ColumnWidth = 1
  Columns("G:J").ColumnWidth = 11
  
  'Changes from Workday color to Custom ORANGE
  With Range("A15:E16,G15:J15,G16,H16,I16,J16").Interior
    .Pattern = xlSolid
    .PatternColor = 15575861
    .Color = 150780
    .TintAndShade = 0
    .PatternTintAndShade = 0
  End With
  
  'Wraps Text for Headers
  With Range("A16:J16")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With
  
  'Adds THICK Border to Section Headers
  With Range("A15:A16,B15:E16,G15:J16")
    .Borders.LineStyle = xlContinuous
    .Borders.Color = -16777216
    .Borders.TintAndShade = 0
    .Borders.Weight = xlMedium
    Range("A15:A16").Borders(xlInsideHorizontal).LineStyle = xlNone
  End With

  'Adds THICK Borders on each large section
  With Range("A17:A78,B17:E78,G17:J78")
    .Borders.LineStyle = xlContinuous
    .Borders.ColorIndex = 0
    .Borders.TintAndShade = 0
    .Borders.Weight = xlMedium
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
  End With
  
  'Adds 10 spaces on LAST Total
  Range("A78").Value = " Total: Adventist HealthCare, Inc."
  
  'Page Layout and Delete Header Rows
  Range("B19:E19,G19:J19,B78:E78,G78:J78").NumberFormat = "$ #,##0,;$ (#,##0,)"
  Rows("1:3").Delete Shift:=xlUp
  Range("B14").Select
  ActiveWindow.FreezePanes = True
  ActiveSheet.PageSetup.PrintArea = "$A$1:$J$75"
  'Application.PrintCommunication = False
  With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
    .LeftHeader = ""
    .CenterHeader = ""
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.5)
    .RightMargin = Application.InchesToPoints(0.5)
    .TopMargin = Application.InchesToPoints(0.5)
    .BottomMargin = Application.InchesToPoints(0.5)
    .HeaderMargin = Application.InchesToPoints(0)
    .FooterMargin = Application.InchesToPoints(0)
    .PrintHeadings = False
    .PrintGridlines = False
    .PrintComments = xlPrintNoComments
    .PrintQuality = 600
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlLandscape
    .Draft = False
    .PaperSize = xlPaperLetter
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .PrintErrors = xlPrintErrorsDisplayed
    .OddAndEvenPagesHeaderFooter = False
    .DifferentFirstPageHeaderFooter = False
    .ScaleWithDocHeaderFooter = True
    .AlignMarginsHeaderFooter = True
    .EvenPage.LeftHeader.Text = ""
    .EvenPage.CenterHeader.Text = ""
    .EvenPage.RightHeader.Text = ""
    .EvenPage.LeftFooter.Text = ""
    .EvenPage.CenterFooter.Text = ""
    .EvenPage.RightFooter.Text = ""
    .FirstPage.LeftHeader.Text = ""
    .FirstPage.CenterHeader.Text = ""
    .FirstPage.RightHeader.Text = ""
    .FirstPage.LeftFooter.Text = ""
    .FirstPage.CenterFooter.Text = ""
    .FirstPage.RightFooter.Text = ""
  End With
  
  'Header Font Style, Size, and Date
  With Range("A1").Font
    .FontStyle = "Bold"
    .Size = 14
  End With
  
  With Range("A2:A3, A7:A10").Font
    .FontStyle = "Bold"
    .Size = 12
  End With
  
  Range("A3").FormulaR1C1 = "=R[6]C"
  Application.ScreenUpdating = True
End Sub

MACRO to search for specific text and format entire row

Shree_Finance18

New Member

Excel Facts

DanteAmor

Well-known Member

Shree_Finance18

New Member

DanteAmor

Well-known Member

Shree_Finance18

New Member

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

MACRO to search for specific text and format entire row

Shree_Finance18

New Member

Excel Facts

DanteAmor

Well-known Member

Shree_Finance18

New Member

DanteAmor

Well-known Member

Shree_Finance18

New Member

DanteAmor

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock