I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

I modify it a little and it work great, however, I what to change the color
of the cells but its not working. Please see below (it does not seem to like
".Interior" for some resone:
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.Interior.ColorIndex = 13

"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

Sorry, I also wanted to add a border around the select cells.

Thanks!


"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

I don't know if you want to color the font or the cell - this includes both

Sub AAB()
Dim r As Range
Set r = Cells.Find(What:="Grand Total", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not r Is Nothing Then
With Range("A" & r.Row & ":W" & r.Row)
With .Font
.Bold = True
.Size = 12
.ColorIndex = xlAutomatic
End With
.Interior.ColorIndex = 13
.BorderAround Weight:=xlMedium
End With
Else
MsgBox "'Grand total' not found"
End If
End Sub

--
Regards,
Tom Ogilvy


"pgarcia" wrote:

Sorry, I also wanted to add a border around the select cells.

Thanks!


"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

I get a "Complie error:" "Invalid or unqualified reference" and it stops at
".Interior.ColorIndex = 13".
Any ideas?

"Tom Ogilvy" wrote:

I don't know if you want to color the font or the cell - this includes both

Sub AAB()
Dim r As Range
Set r = Cells.Find(What:="Grand Total", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not r Is Nothing Then
With Range("A" & r.Row & ":W" & r.Row)
With .Font
.Bold = True
.Size = 12
.ColorIndex = xlAutomatic
End With
.Interior.ColorIndex = 13
.BorderAround Weight:=xlMedium
End With
Else
MsgBox "'Grand total' not found"
End If
End Sub

--
Regards,
Tom Ogilvy


"pgarcia" wrote:

Sorry, I also wanted to add a border around the select cells.

Thanks!


"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

I think I'd put a named range on "Grand Total" if it's possible. Let's say
it's called (!) "Grand_Total" This is the code I'd use

Dim myRange As Range
Dim aWS as worksheet

Set aws = activesheet
Set myRange = Nothing
On Error Resume Next
Set myRange = aws.Range("Grand_Total")
On Error GoTo 0
If Not myRange Is Nothing Then

With aws.Range("A" & myRange.Row & ":W" & myRange.Row)
.BorderAround Weight:=xlThin
.Interior.ColorIndex = 36
End With
End If

--
HTH,
Barb Reinhardt



"pgarcia" wrote:

I get a "Complie error:" "Invalid or unqualified reference" and it stops at
".Interior.ColorIndex = 13".
Any ideas?

"Tom Ogilvy" wrote:

I don't know if you want to color the font or the cell - this includes both

Sub AAB()
Dim r As Range
Set r = Cells.Find(What:="Grand Total", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not r Is Nothing Then
With Range("A" & r.Row & ":W" & r.Row)
With .Font
.Bold = True
.Size = 12
.ColorIndex = xlAutomatic
End With
.Interior.ColorIndex = 13
.BorderAround Weight:=xlMedium
End With
Else
MsgBox "'Grand total' not found"
End If
End Sub

--
Regards,
Tom Ogilvy


"pgarcia" wrote:

Sorry, I also wanted to add a border around the select cells.

Thanks!


"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

Best I can say is you screwed up the code then because that was copied out of
a module where it worked perfectly. Best I can give you is tested solutions.

--
Regards,
Tom Ogilvy



"pgarcia" wrote:

I get a "Complie error:" "Invalid or unqualified reference" and it stops at
".Interior.ColorIndex = 13".
Any ideas?

"Tom Ogilvy" wrote:

I don't know if you want to color the font or the cell - this includes both

Sub AAB()
Dim r As Range
Set r = Cells.Find(What:="Grand Total", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not r Is Nothing Then
With Range("A" & r.Row & ":W" & r.Row)
With .Font
.Bold = True
.Size = 12
.ColorIndex = xlAutomatic
End With
.Interior.ColorIndex = 13
.BorderAround Weight:=xlMedium
End With
Else
MsgBox "'Grand total' not found"
End If
End Sub

--
Regards,
Tom Ogilvy


"pgarcia" wrote:

Sorry, I also wanted to add a border around the select cells.

Thanks!


"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

Hello agina. Thanks for the below code.
I'm using this code again but for something elso. Whith the below code, how
can I selecet the next row and delete all infomaton from that row on down?

Thanks

"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Range(Cells(r + 1, 1), Cells(65536, 1)).EntireRow.Delete
End Sub

pgarcia wrote:
Hello agina. Thanks for the below code.
I'm using this code again but for something elso. Whith the below code, how
can I selecet the next row and delete all infomaton from that row on down?

Thanks

"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

Awesome!!!

"JW" wrote:

Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Range(Cells(r + 1, 1), Cells(65536, 1)).EntireRow.Delete
End Sub

pgarcia wrote:
Hello agina. Thanks for the below code.
I'm using this code again but for something elso. Whith the below code, how
can I selecet the next row and delete all infomaton from that row on down?

Thanks

"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

Unrelated, but do you...
I have a spread sheet with 20 colums. I colum a there is a list of East,
West and South. I need to go down to the last East in the list and inster a
row. Same for West and South.

Thanks

"JW" wrote:

Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Range(Cells(r + 1, 1), Cells(65536, 1)).EntireRow.Delete
End Sub

pgarcia wrote:
Hello agina. Thanks for the below code.
I'm using this code again but for something elso. Whith the below code, how
can I selecet the next row and delete all infomaton from that row on down?

Thanks

"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks

This will cycle through all the used cells in column A, starting in
row 2, and insert a blank line whenever the value changes. I believe
this is what you are after. If not, just let me know.
Sub foo()
BotRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To BotRow
If Cells(i, 1).Value < Cells(i, 1).Offset(-1, 0).Value Then
Cells(i, 1).EntireRow.Insert
BotRow = BotRow + 1
i = i + 1
End If
Next i
End Sub

On Sep 10, 5:32 pm, pgarcia wrote:
Unrelated, but do you...
I have a spread sheet with 20 colums. I colum a there is a list of East,
West and South. I need to go down to the last East in the list and inster a
row. Same for West and South.

Thanks

"JW" wrote:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
Range(Cells(r + 1, 1), Cells(65536, 1)).EntireRow.Delete
End Sub

pgarcia wrote:
Hello agina. Thanks for the below code.
I'm using this code again but for something elso. Whith the below code, how
can I selecet the next row and delete all infomaton from that row on down?

Thanks

"JW" wrote:

Is "Grand Total" only found one time in your whole worksheet? If so:
Sub foofer()
Dim r As Long
r = Cells.Find(What:="Grand Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
With Range("A" & r & ":W" & r).Font
.Bold = True
.Size = 12
.ColorIndex = 5
End With
End Sub

The above sub will do what you want without physically going to the
row containing Grand Total. If you want it to do that, you could just
change the .Row to a .Activate and establish the row then.

HTH
-Jeff-
pgarcia wrote:
I have a cell in D2945 (changes daily). In that cell it reads "Grand Total".
I would like to be able to go to that cell, then go to the begining of that
row, selece row A-W, bold, change color to blue and font 12 points. Does
anyone know how to do this?
Thanks