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

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)
.BorderAround Weight:=xlMedium
.Interior.ColorIndex = 37
.Font.Bold = True
.Font.Size = 12
End WithI got it. I rewrote the code as such:

Thanks everone.


"Barb Reinhardt" wrote:

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

No worrys, I fixed:
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)
.BorderAround Weight:=xlMedium
.Interior.ColorIndex = 37
.Font.Bold = True
.Font.Size = 12
End With

Thanks for the positve feed back. ]:-))

"Tom Ogilvy" wrote:

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

I realy need to take a VB class or two. That was great. Could I ask just two
more things? The first, when it gets to the last data in the colum, it does
not insert a row. And last, could I go to that emtpy cell in that colum and
inset "East Total", "South Total" and "West Total"?

"JW" wrote:

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

JW, why are you not a MVP?
Any who. Could I pick your brain on one more thing? I have list that I
compare to a list from the prevision day. Now, I line the two side by side. I
do a simple formula of A1-C1 in cell B1. If the amount is €œ0€�, I dont do
anything to the rows. I move to the next cell and put the formula in again (I
really just drag it down). Now if the amount is positive, e.g. $15, then I
insert a row to the right of that amount and it moves the data down. Then I
go down to the next cell and put the formula in again. If its a negative
number, e.g. -$15, I then insert a row to the left of that amount and it
moves the data down. I go down to the next cell and input the formula again.
Do you know of a way I can do this via VB? Am I making since?
Thanks

Tweaked it a little. This should work for the last data in column
issue and to add the "East Total" into the inserted row:
Sub foorev()
botrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For i = botrow To 3 Step -1
If Cells(i, 1).Value < Cells(i, 1).Offset(-1, 0).Value Then
Cells(i, 1).EntireRow.Insert
Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0).Value & "
Total"
End If
Next i
End Sub

As for VB classes, FWIW, I've never taken a single class on VB or
VBA. Everything I know is self taught. Not saying that classes are
a bad thing, because they most certainly are not. But through
practice and confidence, good VB skills can be acheived.


pgarcia wrote:
I realy need to take a VB class or two. That was great. Could I ask just two
more things? The first, when it gets to the last data in the colum, it does
not insert a row. And last, could I go to that emtpy cell in that colum and
inset "East Total", "South Total" and "West Total"?

"JW" wrote:

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

Thanks for the compliment on the MVP thing. I have no idea how one
even becomes an MVP. lol

I kind of follow what you mean, but just kind of. Let me see if I
have this right:
1 - Column A contains a list
2 - Column C contains a list
3 - Column B contains the formula A-C
4 - If the forumla in col B works out to 0, do nothing
5 - If the formula in col B works out to positive, insert a cell in
column C, forcing the other numbers in that column to shift down
6 - If the formula in col B works out to positive, insert a cell in
column A, forcing the other numbers in that column to shift down

Am I correct in this thinking? If so, shouldn't be a big deal to do.
If you would like, you can send me a copy of your spreadsheet and I
will take a look at real data and come up witha solution. Or, you can
provide a dummy example here.

pgarcia wrote:
JW, why are you not a MVP?
Any who. Could I pick your brain on one more thing? I have list that I
compare to a list from the prevision day. Now, I line the two side by side. I
do a simple formula of A1-C1 in cell B1. If the amount is "0", I don't do
anything to the rows. I move to the next cell and put the formula in again (I
really just drag it down). Now if the amount is positive, e.g. $15, then I
insert a row to the right of that amount and it moves the data down. Then I
go down to the next cell and put the formula in again. If it's a negative
number, e.g. -$15, I then insert a row to the left of that amount and it
moves the data down. I go down to the next cell and input the formula again.
Do you know of a way I can do this via VB? Am I making since?
Thanks

I'll send it over and a video so you can see what it is I'm doing.

Thanks!!!

"JW" wrote:

Thanks for the compliment on the MVP thing. I have no idea how one
even becomes an MVP. lol

I kind of follow what you mean, but just kind of. Let me see if I
have this right:
1 - Column A contains a list
2 - Column C contains a list
3 - Column B contains the formula A-C
4 - If the forumla in col B works out to 0, do nothing
5 - If the formula in col B works out to positive, insert a cell in
column C, forcing the other numbers in that column to shift down
6 - If the formula in col B works out to positive, insert a cell in
column A, forcing the other numbers in that column to shift down

Am I correct in this thinking? If so, shouldn't be a big deal to do.
If you would like, you can send me a copy of your spreadsheet and I
will take a look at real data and come up witha solution. Or, you can
provide a dummy example here.

pgarcia wrote:
JW, why are you not a MVP?
Any who. Could I pick your brain on one more thing? I have list that I
compare to a list from the prevision day. Now, I line the two side by side. I
do a simple formula of A1-C1 in cell B1. If the amount is "0", I don't do
anything to the rows. I move to the next cell and put the formula in again (I
really just drag it down). Now if the amount is positive, e.g. $15, then I
insert a row to the right of that amount and it moves the data down. Then I
go down to the next cell and put the formula in again. If it's a negative
number, e.g. -$15, I then insert a row to the left of that amount and it
moves the data down. I go down to the next cell and input the formula again.
Do you know of a way I can do this via VB? Am I making since?
Thanks

e-mail returned.
;-)
pgarcia wrote:
I'll send it over and a video so you can see what it is I'm doing.

Thanks!!!

"JW" wrote:

Thanks for the compliment on the MVP thing. I have no idea how one
even becomes an MVP. lol

I kind of follow what you mean, but just kind of. Let me see if I
have this right:
1 - Column A contains a list
2 - Column C contains a list
3 - Column B contains the formula A-C
4 - If the forumla in col B works out to 0, do nothing
5 - If the formula in col B works out to positive, insert a cell in
column C, forcing the other numbers in that column to shift down
6 - If the formula in col B works out to positive, insert a cell in
column A, forcing the other numbers in that column to shift down

Am I correct in this thinking? If so, shouldn't be a big deal to do.
If you would like, you can send me a copy of your spreadsheet and I
will take a look at real data and come up witha solution. Or, you can
provide a dummy example here.

pgarcia wrote:
JW, why are you not a MVP?
Any who. Could I pick your brain on one more thing? I have list that I
compare to a list from the prevision day. Now, I line the two side by side. I
do a simple formula of A1-C1 in cell B1. If the amount is "0", I don't do
anything to the rows. I move to the next cell and put the formula in again (I
really just drag it down). Now if the amount is positive, e.g. $15, then I
insert a row to the right of that amount and it moves the data down. Then I
go down to the next cell and put the formula in again. If it's a negative
number, e.g. -$15, I then insert a row to the left of that amount and it
moves the data down. I go down to the next cell and input the formula again.
Do you know of a way I can do this via VB? Am I making since?
Thanks

Cool. But it would not inset the text part. When I removed it, it did work
adding the last row in.
Thanks


Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0).Value & "
Total"

"JW" wrote:

Tweaked it a little. This should work for the last data in column
issue and to add the "East Total" into the inserted row:
Sub foorev()
botrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For i = botrow To 3 Step -1
If Cells(i, 1).Value < Cells(i, 1).Offset(-1, 0).Value Then
Cells(i, 1).EntireRow.Insert
Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0).Value & "
Total"
End If
Next i
End Sub

As for VB classes, FWIW, I've never taken a single class on VB or
VBA. Everything I know is self taught. Not saying that classes are
a bad thing, because they most certainly are not. But through
practice and confidence, good VB skills can be acheived.


pgarcia wrote:
I realy need to take a VB class or two. That was great. Could I ask just two
more things? The first, when it gets to the last data in the colum, it does
not insert a row. And last, could I go to that emtpy cell in that colum and
inset "East Total", "South Total" and "West Total"?

"JW" wrote:

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

It works on my end for insterting the text. Are you receiving an
error? The complete line should be:
Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0).Value & "Total"
pgarcia wrote:
Cool. But it would not inset the text part. When I removed it, it did work
adding the last row in.
Thanks


Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0).Value & "
Total"

"JW" wrote:

Tweaked it a little. This should work for the last data in column
issue and to add the "East Total" into the inserted row:
Sub foorev()
botrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For i = botrow To 3 Step -1
If Cells(i, 1).Value < Cells(i, 1).Offset(-1, 0).Value Then
Cells(i, 1).EntireRow.Insert
Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0).Value & "
Total"
End If
Next i
End Sub

As for VB classes, FWIW, I've never taken a single class on VB or
VBA. Everything I know is self taught. Not saying that classes are
a bad thing, because they most certainly are not. But through
practice and confidence, good VB skills can be acheived.


pgarcia wrote:
I realy need to take a VB class or two. That was great. Could I ask just two
more things? The first, when it gets to the last data in the colum, it does
not insert a row. And last, could I go to that emtpy cell in that colum and
inset "East Total", "South Total" and "West Total"?

"JW" wrote:

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

Sorry, it did work. I had to clean up the work sheets. They go a little masy.

"JW" wrote:

It works on my end for insterting the text. Are you receiving an
error? The complete line should be:
Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0).Value & "Total"
pgarcia wrote:
Cool. But it would not inset the text part. When I removed it, it did work
adding the last row in.
Thanks


Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0).Value & "
Total"

"JW" wrote:

Tweaked it a little. This should work for the last data in column
issue and to add the "East Total" into the inserted row:
Sub foorev()
botrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
For i = botrow To 3 Step -1
If Cells(i, 1).Value < Cells(i, 1).Offset(-1, 0).Value Then
Cells(i, 1).EntireRow.Insert
Cells(i, 1).Value = Cells(i, 1).Offset(-1, 0).Value & "
Total"
End If
Next i
End Sub

As for VB classes, FWIW, I've never taken a single class on VB or
VBA. Everything I know is self taught. Not saying that classes are
a bad thing, because they most certainly are not. But through
practice and confidence, good VB skills can be acheived.


pgarcia wrote:
I realy need to take a VB class or two. That was great. Could I ask just two
more things? The first, when it gets to the last data in the colum, it does
not insert a row. And last, could I go to that emtpy cell in that colum and
inset "East Total", "South Total" and "West Total"?

"JW" wrote:

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