Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find string and format 54 columns to right
Hi All
I'm trying to write code that looks in column A and finds the word "total" (which has words before or after it in the same cell, plus could be in upper or lower case). Whenever "total" is found I need it and the 56 columns to it's right selected and formatted with the following: With Selection .Font.FontStyle = "Bold" .Font.ColorIndex = 2 .Interior.ColorIndex = 47 End With I tried this code, but it won't pick up the "string" in a cell and I don't know how to change the code so it does that: Columns("A:A").Select For Each oCell In ActiveSheet.UsedRange If oCell = "*Total*" Then Range(oCell.Offset(1, 0), oCell.Offset(0, 54)).Select With Selection .Font.FontStyle = "Bold" .Font.ColorIndex = 2 .Interior.ColorIndex = 47 End With End If Next oCell Any help would be greatly appreciated. -- Thank for your help BeSmart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find string and format 54 columns to right
why not just use Conditional Formatting?
Select column 56 and select Format/Conditional Formatting set "Formula Is" to =($A1="Total") "BeSmart" wrote: Hi All I'm trying to write code that looks in column A and finds the word "total" (which has words before or after it in the same cell, plus could be in upper or lower case). Whenever "total" is found I need it and the 56 columns to it's right selected and formatted with the following: With Selection .Font.FontStyle = "Bold" .Font.ColorIndex = 2 .Interior.ColorIndex = 47 End With I tried this code, but it won't pick up the "string" in a cell and I don't know how to change the code so it does that: Columns("A:A").Select For Each oCell In ActiveSheet.UsedRange If oCell = "*Total*" Then Range(oCell.Offset(1, 0), oCell.Offset(0, 54)).Select With Selection .Font.FontStyle = "Bold" .Font.ColorIndex = 2 .Interior.ColorIndex = 47 End With End If Next oCell Any help would be greatly appreciated. -- Thank for your help BeSmart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find string and format 54 columns to right
Hi,
does this work? change: If oCell = "*Total*" Then to: If oCell like "*Total*" Then ? OJ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find string and format 54 columns to right
IF you do want a VBA solution, then this is it:
Sub FormatTotal() Dim address As String Dim found As Range Set found = Range("A:A").Find("Total") If Not found Is Nothing Then address = found.address Do With Cells(found.Row, 56) .Font.FontStyle = "Bold" .Font.ColorIndex = 2 .Interior.ColorIndex = 47 End With Set found = Range("A:A").FindNext(found) Loop Until found.address = address End If End Sub "Patrick Molloy" wrote: why not just use Conditional Formatting? Select column 56 and select Format/Conditional Formatting set "Formula Is" to =($A1="Total") "BeSmart" wrote: Hi All I'm trying to write code that looks in column A and finds the word "total" (which has words before or after it in the same cell, plus could be in upper or lower case). Whenever "total" is found I need it and the 56 columns to it's right selected and formatted with the following: With Selection .Font.FontStyle = "Bold" .Font.ColorIndex = 2 .Interior.ColorIndex = 47 End With I tried this code, but it won't pick up the "string" in a cell and I don't know how to change the code so it does that: Columns("A:A").Select For Each oCell In ActiveSheet.UsedRange If oCell = "*Total*" Then Range(oCell.Offset(1, 0), oCell.Offset(0, 54)).Select With Selection .Font.FontStyle = "Bold" .Font.ColorIndex = 2 .Interior.ColorIndex = 47 End With End If Next oCell Any help would be greatly appreciated. -- Thank for your help BeSmart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find String in another string - only between spaces | Excel Worksheet Functions | |||
Find Many String options in ONE String | Excel Worksheet Functions | |||
Q: find range, listbox - format and fit columns | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming | |||
find a string inside another string | Excel Programming |