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

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

Find string and format 54 columns to right
 

Hi,
does this work?
change:
If oCell = "*Total*" Then
to:
If oCell like "*Total*" Then

?
OJ

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

Find string and format 54 columns to right
 

Besmart

You have 54 columns in the subject and 56 in the text. This does 56

Sub FindTotal()
Dim lLastRow As Long
Dim myCell As Range
lLastRow = Range("A65536").End(xlUp).Row
For Each myCell In Range("A1:A" & lLastRow)
If InStr(1, UCase(myCell.Value), "TOTAL") 0 Then
With myCell.Resize(1, 56)
.Font.Bold = True
.Font.ColorIndex = 2
.Interior.ColorIndex = 47
End With
End If
Next myCell
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"BeSmart" wrote in message
...
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