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

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

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

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

?
OJ

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
[email protected]HIS


"BeSmart" <[email protected]> wrote in message
news:[email protected]...
> 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