Locat a cell

[Guest]

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]

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-

 

[Guest]

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

 

[Guest]

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

Thanks!

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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. ])

 

[Guest]

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]

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

 

[Guest]

Awesome!!!

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

 

[Guest]

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]

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

 

[Guest]

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"?

 

[Guest]

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

 

[JW]

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.

 

[JW]

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.

 

[Guest]

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

Thanks!!!