Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Row based on Cell Content
I have several columns/rows with data. I would like a macro to go and
find the cells that contain the word "Total" and select the row and format it (bold, background color = 37) For example: A B C D row1 10 10 2.7 3.5 row2 11 Total 2.7 3.5 row3 12 12 total 4.5 The word "Total" may appear in any column or any row within the data range. Some rows may not have the word total. I'd like the macro to find the word "Total" and then highlight from Column A through the end of the row and format it. I have a macro that will bold from the word "Total to the end of row." Here's the code for that: Sub BoldTotals() ' Macro finds all cells containing the word TOTAL and applies ' Bold, underline and shading to the row Dim LastColumn As Integer LastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column For Each C In ActiveSheet.UsedRange If C Like "*TOTAL*" Or C Like "*Total*" Then ' select from current cell to end of range Range(C, C.Offset(0, (LastColumn - C.Column))).Activate ' set those cells to bold With Selection .Font.Bold = True .Interior.ColorIndex = 37 .Borders(xlTop).Weight = xlThin .Borders(xlBottom).Weight = xlMedium End With End If Next Application.ScreenUpdating = True End Sub Rather than selecting from the word Total to the right (end of row), I'd like to highlight and format the whole row. Any suggestions? This was a macro given to me and I'm not that good in scripting these. Thanks for any help! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Row based on Cell Content
Try this
Dim cRows As Long Dim cCols As Long Dim i As Long, j As Long With ActiveSheet cRows = .UsedRange.Rows.Count cCols = .UsedRange.Columns.Count For i = 1 To cRows For j = 1 To cCols With .UsedRange If LCase(.Cells(i, j).Value) = "total" Then With .Range(.Cells(i, 1), .Cells(i, cCols)) .Interior.ColorIndex = 15 .Borders(xlTop).Weight = xlThin .Borders(xlBottom).Weight = xlMedium End With Exit For End If End With Next j Next i End With -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "SFrongillo_Lib" wrote in message ... I have several columns/rows with data. I would like a macro to go and find the cells that contain the word "Total" and select the row and format it (bold, background color = 37) For example: A B C D row1 10 10 2.7 3.5 row2 11 Total 2.7 3.5 row3 12 12 total 4.5 The word "Total" may appear in any column or any row within the data range. Some rows may not have the word total. I'd like the macro to find the word "Total" and then highlight from Column A through the end of the row and format it. I have a macro that will bold from the word "Total to the end of row." Here's the code for that: Sub BoldTotals() ' Macro finds all cells containing the word TOTAL and applies ' Bold, underline and shading to the row Dim LastColumn As Integer LastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column For Each C In ActiveSheet.UsedRange If C Like "*TOTAL*" Or C Like "*Total*" Then ' select from current cell to end of range Range(C, C.Offset(0, (LastColumn - C.Column))).Activate ' set those cells to bold With Selection Font.Bold = True Interior.ColorIndex = 37 Borders(xlTop).Weight = xlThin Borders(xlBottom).Weight = xlMedium End With End If Next Application.ScreenUpdating = True End Sub Rather than selecting from the word Total to the right (end of row), I'd like to highlight and format the whole row. Any suggestions? This was a macro given to me and I'm not that good in scripting these. Thanks for any help! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Row based on Cell Content
Note1: the option compare is needed for like operator, and should go in the TOP of the module's code= thus applies to all code in module. Note2: the usedrange may not start in ColumnA or Row1, and continues as far as the last cell with formatting and/or data Option Explicit Option Compare Text Sub BoldTotals() ' Macro finds all cells containing the word TOTAL and applies ' Bold, underline and shading to the row Dim c As Range Application.ScreenUpdating = False With ActiveSheet For Each c In .UsedRange.Cells If c.Text Like "*total*" Then With Intersect(.UsedRange, c.EntireRow) .Font.Bold = True .Interior.ColorIndex = 37 .Borders(xlTop).Weight = xlThin .Borders(xlBottom).Weight = xlMedium End With End If Next End With Application.ScreenUpdating = True End Sub cheerz! keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool SFrongillo_Lib wrote: I have several columns/rows with data. I would like a macro to go and find the cells that contain the word "Total" and select the row and format it (bold, background color = 37) For example: A B C D row1 10 10 2.7 3.5 row2 11 Total 2.7 3.5 row3 12 12 total 4.5 The word "Total" may appear in any column or any row within the data range. Some rows may not have the word total. I'd like the macro to find the word "Total" and then highlight from Column A through the end of the row and format it. I have a macro that will bold from the word "Total to the end of row." Here's the code for that: Sub BoldTotals() ' Macro finds all cells containing the word TOTAL and applies ' Bold, underline and shading to the row Dim LastColumn As Integer LastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column For Each C In ActiveSheet.UsedRange If C Like "*TOTAL*" Or C Like "*Total*" Then ' select from current cell to end of range Range(C, C.Offset(0, (LastColumn - C.Column))).Activate ' set those cells to bold With Selection Font.Bold = True Interior.ColorIndex = 37 Borders(xlTop).Weight = xlThin Borders(xlBottom).Weight = xlMedium End With End If Next Application.ScreenUpdating = True End Sub Rather than selecting from the word Total to the right (end of row), I'd like to highlight and format the whole row. Any suggestions? This was a macro given to me and I'm not that good in scripting these. Thanks for any help! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Row based on Cell Content
Thanks! It worked!! This is going to make my life a little easier!!
------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically format cell based on content of another cell | Excel Worksheet Functions | |||
Copy row based on cell content | New Users to Excel | |||
how do I change the content of one cell based on another? | Excel Discussion (Misc queries) | |||
Colour Cell based on Content | Excel Worksheet Functions | |||
Cond Format & helper-cell based "duplicate rec" tricked by content | Excel Discussion (Misc queries) |