Originally Posted by solring
That's because you're telling it to start on row 31 by using "lastrowsheetone". Just use the "x-1" part...
Thanks solring!
I tried replacing lastrowsheetone with a1, "a1", "$A$1". Type mismatch...
Im not sure what type of value it needs. I will keep plugging away at it!
Also I have a cell with 1:05 PM ET
How can i search from n1:n100 , when it finds a cell with time , I would like to
Change colors for every time it finds time
Range("a11").Interior.Color = RGB(25, 25, 112)
Range("a1
I tried something like this
Set FoundCell =Range("n1:n100").Find(what:=Time)
but no luck
Maybe it would be easier to say something like
if a1 contains "at" then
Range("a1: p1").Interior.Color = RGB(25, 25, 112)
Range("a1: p1").Font.Color = vbWhite
or would i need to use a $a$1?
the keyword "at" could appear until a100
I have this
Dim cell As Object
For Each cell In ActiveSheet.UsedRange
If cell.Value = "STARTERS" Then
'cell.Interior.ColorIndex = 36
Range("A1: p1").Interior.Color = RGB(25, 25, 112)
Range("A1: p1").Font.Color = vbWhite
End If
But it only puts does the color in a1: p1
where I would like the color to be up 2 rows then fill across
Both rows and columns are represented in the .Cells function by number. So, "A1" = row 1, column 1. "Z43" = row 43, column 26.I tried replacing lastrowsheetone with a1, "a1", "$A$1". Type mismatch...
Im not sure what type of value it needs. I will keep plugging away at it!
B_rad
Not exactly sure what your trying to do but this might be another option. Create a sheet formatted how you like as far as colours fonts etc. import your data/web query to a temp sheet and then copy and paste it to your formatted pretty sheet. Just make sure your imported data is in the same cells as your formatted sheet. They must coordinate.
Also when you paste use pastespecial values
Im just trying to add some colours to the table but only when there are certain key words.
Your way does work oil but, if there are more games then others it won't have the proper colours, if I add the colours to too many rows there may not be data.
It would be nice if there is an option to import the colours from the site with the table
Got it Oil...
This will search row a1 through a100. Look for the word STARTERS. Once found it will move up 2 rows and color the entire row as well as change the font color.
Dim r As Range
Dim i As Long
Set r = Range("A1:A100")
For i = r.Rows.Count To 1 Step -1
With r.Cells(i, 1)
If .Value = "STARTERS" Then
Range("A" & i - 2).EntireRow.Interior.Color = RGB(25, 25, 112)
Range("A" & i - 2).EntireRow.Font.Color = vbWhite
End If
End With
Next I
That's because you're telling it to start on row 31 by using "lastrowsheetone". Just use the "x-1" part...
I ended up just erasing the last part and moved my formulas to the page that brings in the table.
Another thought would be adding conditional formatting to your 'final' sheet or wherever the data is pasted to from the temp sheet.Got it Oil...
This will search row a1 through a100. Look for the word STARTERS. Once found it will move up 2 rows and color the entire row as well as change the font color.
Dim r As Range
Dim i As Long
Set r = Range("A1:A100")
For i = r.Rows.Count To 1 Step -1
With r.Cells(i, 1)
If .Value = "STARTERS" Then
Range("A" & i - 2).EntireRow.Interior.Color = RGB(25, 25, 112)
Range("A" & i - 2).EntireRow.Font.Color = vbWhite
End If
End With
Next I
This is my current copy selection
Worksheets("Test").Range("A22
this is a way i found to get to a cell and clear it.
Dim cell As Range
For Each cell In [a:a]
If cell.Value = "SPORTS" Then cell.ClearContents 'put any value you want here
Next cell
What i would like to do is use them both together, to either delete everything after cell.value or use cell.value in my copy instead of p99
have having a hard time at combining everything
Instead of copying I just deleted un wanted stuff and formatted on the main page. At the end of my code it renames the sheet.
So I guess you could say I figured it out.
Text Splitting...
Pittsburgh Pirates at Chicago Cubs
Milwaukee Brewers at Philadelphia Phillies
New York Mets at Atlanta Braves
That's a1 trough a3
I would like to have the home teams in r1 through r3 and visiting teams in s1 through s3.
Matchups will not always be in the same order.
It seems that for some teams what I tried works, but with others it doesn't.
New York Mets at Atlanta Braves =RIGHT(A59,LEN(A59)-(1)-FIND("at",A59))
that works to get Atlanta
Pittsburgh Pirates at Chicago Cubs =RIGHT(A64,LEN(A64)-(1)-FIND("at",A64))
same formula doesn't work to get Chicago
Result= es at Chicago Cubs
