Hi,
Is there any way to use a macro to find a word example "Total" in a
column example "C:C" and when it finds the word it will color that row
say "Blue" and then find next and if find more do the same all the way
to the end of the column.

Thanks

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub marine()
Dim myrange, MyRange1 As Range
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Set myrange = Range("C1:C" & lastrow)
For Each c In myrange
If UCase(c.Value) = "TOTAL" Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
MyRange1.Interior.ColorIndex = 41
End Sub

Mike

"(E-Mail Removed)" wrote:

> Hi,
> Is there any way to use a macro to find a word example "Total" in a
> column example "C:C" and when it finds the word it will color that row
> say "Blue" and then find next and if find more do the same all the way
> to the end of the column.
>
> Thanks
>

try this. change sheet25 to suit the name of your sheet and 500 to suit your
last row

Sub colortotalrow()
With Worksheets("sheet25").Range("c1:c500")
Set c = .Find("Total", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Rows(c.Row).Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing _
And c.Address <> firstAddress
End If
End With

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:c990c4dc-4557-47a7-b798-(E-Mail Removed)...
> Hi,
> Is there any way to use a macro to find a word example "Total" in a
> column example "C:C" and when it finds the word it will color that row
> say "Blue" and then find next and if find more do the same all the way
> to the end of the column.
>
> Thanks

On Jun 21, 6:45*am, "Don Guillett" <dguille...@austin.rr.com> wrote:
> try this. change sheet25 to suit the name of your sheet and 500 to suit your
> last row
>
> Sub colortotalrow()
> With Worksheets("sheet25").Range("c1:c500")
> * * Set c = .Find("Total", LookIn:=xlValues)
> * * If Not c Is Nothing Then
> * * * * firstAddress = c.Address
> * * * * Do
> * * * * *Rows(c.Row).Interior.ColorIndex = 6
> * * * * Set c = .FindNext(c)
> * * * * Loop While Not c Is Nothing _
> * * * * And c.Address <> firstAddress
> * * End If
> End With
>
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com<marc...@excite.com> wrote in message
>
> news:c990c4dc-4557-47a7-b798-(E-Mail Removed)...
>
>
>
> > Hi,
> > Is there any way to use a macro to find a word example "Total" in a
> > column example "C:C" and when it finds the word it will color that row
> > say "Blue" and then find next and if find more do the same all the way
> > to the end of the column.
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

Hi,
Thanks it works great. Can I make another macro or add to this one,
I need the macro to first find the "Total" in "C:C" then clear the
Borders in that ROW and then place a border on top and bottom of that
ROW and if any cell in that ROW is not empty place a OUTLINE Border.
Thanks

Please TOP post with me

Sub colortotalrow()
With Worksheets("sheet25").Range("c1:c500")
Set c = .Find("Total", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
With Rows(c.Row)
.Interior.ColorIndex = 6
.BorderAround , Weight:=xlMedium
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing _
And c.Address <> firstAddress
End If
End With

End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:4c60148c-3c4d-47a2-b5c7-(E-Mail Removed)...
On Jun 21, 6:45 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
> try this. change sheet25 to suit the name of your sheet and 500 to suit
> your
> last row
>
> Sub colortotalrow()
> With Worksheets("sheet25").Range("c1:c500")
> Set c = .Find("Total", LookIn:=xlValues)
> If Not c Is Nothing Then
> firstAddress = c.Address
> Do
> Rows(c.Row).Interior.ColorIndex = 6
> Set c = .FindNext(c)
> Loop While Not c Is Nothing _
> And c.Address <> firstAddress
> End If
> End With
>
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com<marc...@excite.com> wrote in message
>
> news:c990c4dc-4557-47a7-b798-(E-Mail Removed)...
>
>
>
> > Hi,
> > Is there any way to use a macro to find a word example "Total" in a
> > column example "C:C" and when it finds the word it will color that row
> > say "Blue" and then find next and if find more do the same all the way
> > to the end of the column.
>
> > Thanks- Hide quoted text -
>
> - Show quoted text -

Hi,
Thanks it works great. Can I make another macro or add to this one,
I need the macro to first find the "Total" in "C:C" then clear the
Borders in that ROW and then place a border on top and bottom of that
ROW and if any cell in that ROW is not empty place a OUTLINE Border.
Thanks

On Jun 21, 10:19*am, marc...@excite.com wrote:
> On Jun 21, 6:45*am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
> > try this. change sheet25 to suit the name of your sheet and 500 to suit your
> > last row
>
> > Sub colortotalrow()
> > With Worksheets("sheet25").Range("c1:c500")
> > * * Set c = .Find("Total", LookIn:=xlValues)
> > * * If Not c Is Nothing Then
> > * * * * firstAddress = c.Address
> > * * * * Do
> > * * * * *Rows(c.Row).Interior.ColorIndex = 6
> > * * * * Set c = .FindNext(c)
> > * * * * Loop While Not c Is Nothing _
> > * * * * And c.Address <> firstAddress
> > * * End If
> > End With
>
> > End Sub
>
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com<marc...@excite.com> wrote in message
>
> >news:c990c4dc-4557-47a7-b798-(E-Mail Removed)...
>
> > > Hi,
> > > Is there any way to use a macro to find a word example "Total" in a
> > > column example "C:C" and when it finds the word it will color that row
> > > say "Blue" and then find next and if find more do the same all the way
> > > to the end of the column.
>






Hi,
Thanks it works great. Can I make another macro or add to this one,
I need the macro to first find the "Total" in "C:C" then clear the
Borders in that ROW and then place a border on top and bottom of that
ROW and if any cell in that ROW is not empty place a OUTLINE Border.
Thanks

Did you NOT see my last post with the change and the request to TOP POST


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:81321a7f-87e5-4c18-97f3-(E-Mail Removed)...
On Jun 21, 10:19 am, marc...@excite.com wrote:
> On Jun 21, 6:45 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
> > try this. change sheet25 to suit the name of your sheet and 500 to suit
> > your
> > last row
>
> > Sub colortotalrow()
> > With Worksheets("sheet25").Range("c1:c500")
> > Set c = .Find("Total", LookIn:=xlValues)
> > If Not c Is Nothing Then
> > firstAddress = c.Address
> > Do
> > Rows(c.Row).Interior.ColorIndex = 6
> > Set c = .FindNext(c)
> > Loop While Not c Is Nothing _
> > And c.Address <> firstAddress
> > End If
> > End With
>
> > End Sub
>
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com<marc...@excite.com> wrote in message
>
> >news:c990c4dc-4557-47a7-b798-(E-Mail Removed)...
>
> > > Hi,
> > > Is there any way to use a macro to find a word example "Total" in a
> > > column example "C:C" and when it finds the word it will color that row
> > > say "Blue" and then find next and if find more do the same all the way
> > > to the end of the column.
>






Hi,
Thanks it works great. Can I make another macro or add to this one,
I need the macro to first find the "Total" in "C:C" then clear the
Borders in that ROW and then place a border on top and bottom of that
ROW and if any cell in that ROW is not empty place a OUTLINE Border.
Thanks

On Jun 21, 11:38*am, "Don Guillett" <dguille...@austin.rr.com> wrote:
> Did you NOT see my last post with the change and the request to TOP POST
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com<marc...@excite.com> wrote in message
>
> news:81321a7f-87e5-4c18-97f3-(E-Mail Removed)...
> On Jun 21, 10:19 am, marc...@excite.com wrote:
>
>
>
>
>
> > On Jun 21, 6:45 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
> > > try this. change sheet25 to suit the name of your sheet and 500 to suit
> > > your
> > > last row
>
> > > Sub colortotalrow()
> > > With Worksheets("sheet25").Range("c1:c500")
> > > Set c = .Find("Total", LookIn:=xlValues)
> > > If Not c Is Nothing Then
> > > firstAddress = c.Address
> > > Do
> > > Rows(c.Row).Interior.ColorIndex = 6
> > > Set c = .FindNext(c)
> > > Loop While Not c Is Nothing _
> > > And c.Address <> firstAddress
> > > End If
> > > End With
>
> > > End Sub
>
> > > --
> > > Don Guillett
> > > Microsoft MVP Excel
> > > SalesAid Software
> > > dguille...@austin.rr.com<marc...@excite.com> wrote in message
>
> > >news:c990c4dc-4557-47a7-b798-(E-Mail Removed)....
>
> > > > Hi,
> > > > Is there any way to use a macro to find a word example "Total" in a
> > > > column example "C:C" and when it finds the word it will color that row
> > > > say "Blue" and then find next and if find more do the same all the way
> > > > to the end of the column.
>
> Hi,
> Thanks it works great. Can I make another macro or add to this one,
> I need the macro to first find the "Total" in "C:C" then clear the
> Borders in that ROW and then place a border on top and bottom of that
> ROW and if any cell in that ROW is not empty place a OUTLINE Border.
> Thanks- Hide quoted text -
>
> - Show quoted text -

Hi,
What is a top post?

On Jun 21, 11:38*am, "Don Guillett" <dguille...@austin.rr.com> wrote:
> Did you NOT see my last post with the change and the request to TOP POST
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com<marc...@excite.com> wrote in message
>
> news:81321a7f-87e5-4c18-97f3-(E-Mail Removed)...
> On Jun 21, 10:19 am, marc...@excite.com wrote:
>
>
>
>
>
> > On Jun 21, 6:45 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
> > > try this. change sheet25 to suit the name of your sheet and 500 to suit
> > > your
> > > last row
>
> > > Sub colortotalrow()
> > > With Worksheets("sheet25").Range("c1:c500")
> > > Set c = .Find("Total", LookIn:=xlValues)
> > > If Not c Is Nothing Then
> > > firstAddress = c.Address
> > > Do
> > > Rows(c.Row).Interior.ColorIndex = 6
> > > Set c = .FindNext(c)
> > > Loop While Not c Is Nothing _
> > > And c.Address <> firstAddress
> > > End If
> > > End With
>
> > > End Sub
>
> > > --
> > > Don Guillett
> > > Microsoft MVP Excel
> > > SalesAid Software
> > > dguille...@austin.rr.com<marc...@excite.com> wrote in message
>
> > >news:c990c4dc-4557-47a7-b798-(E-Mail Removed)....
>
> > > > Hi,
> > > > Is there any way to use a macro to find a word example "Total" in a
> > > > column example "C:C" and when it finds the word it will color that row
> > > > say "Blue" and then find next and if find more do the same all the way
> > > > to the end of the column.
>
> Hi,
> Thanks it works great. Can I make another macro or add to this one,
> I need the macro to first find the "Total" in "C:C" then clear the
> Borders in that ROW and then place a border on top and bottom of that
> ROW and if any cell in that ROW is not empty place a OUTLINE Border.
> Thanks- Hide quoted text -
>
> - Show quoted text -

Yes, I see know. Thanks.

On Jun 21, 2:09*pm, marc...@excite.com wrote:
> On Jun 21, 11:38*am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
>
>
>
>
> > Did you NOT see my last post with the change and the request to TOP POST
>
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com<marc...@excite.com> wrote in message
>
> >news:81321a7f-87e5-4c18-97f3-(E-Mail Removed)...
> > On Jun 21, 10:19 am, marc...@excite.com wrote:
>
> > > On Jun 21, 6:45 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
> > > > try this. change sheet25 to suit the name of your sheet and 500 to suit
> > > > your
> > > > last row
>
> > > > Sub colortotalrow()
> > > > With Worksheets("sheet25").Range("c1:c500")
> > > > Set c = .Find("Total", LookIn:=xlValues)
> > > > If Not c Is Nothing Then
> > > > firstAddress = c.Address
> > > > Do
> > > > Rows(c.Row).Interior.ColorIndex = 6
> > > > Set c = .FindNext(c)
> > > > Loop While Not c Is Nothing _
> > > > And c.Address <> firstAddress
> > > > End If
> > > > End With
>
> > > > End Sub
>
> > > > --
> > > > Don Guillett
> > > > Microsoft MVP Excel
> > > > SalesAid Software
> > > > dguille...@austin.rr.com<marc...@excite.com> wrote in message
>
> > > >news:c990c4dc-4557-47a7-b798-(E-Mail Removed)...
>
> > > > > Hi,
> > > > > Is there any way to use a macro to find a word example "Total" ina
> > > > > column example "C:C" and when it finds the word it will color that row
> > > > > say "Blue" and then find next and if find more do the same all the way
> > > > > to the end of the column.
>
> > Hi,
> > Thanks it works great. Can I make another macro or add to this one,
> > I need the macro to first find the "Total" in "C:C" then clear the
> > Borders in that ROW and then place a border on top and bottom of that
> > ROW and if any cell in that ROW is not empty place a OUTLINE Border.
> > Thanks- Hide quoted text -
>
> > - Show quoted text -
>
> Yes, I see know. Thanks.- Hide quoted text -
>
> - Show quoted text -

Hi,
Thanks, How about if any cell in that ROW that we colored that is not
empty place a OUTLINE Border, not only the top and bottom but the
sides too for those cells.
Thanks