Cell Parameters
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Cell Parameters
I'd like code to display/change the color fill of a cell/range.
Also what would be the code to find the upper left cell used on a worksheet.
ETA:
There are column headers followed by blank rows then the data starts, that is the upper left I'd like to find.
ETA: I tried checking the current fill color in the immediate window
worksheets("TradeHistory_20080920110258").range("a6"
,"o6").font.fillcolor
That didn't work
Also what would be the code to find the upper left cell used on a worksheet.
ETA:
There are column headers followed by blank rows then the data starts, that is the upper left I'd like to find.
ETA: I tried checking the current fill color in the immediate window
worksheets("TradeHistory_20080920110258").range("a6"
,"o6").font.fillcolor
That didn't work
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Parameters
1) How many header rows do you have?
2) To get the fill color, use Range(...).Interior.Color
2) To get the fill color, use Range(...).Interior.Color
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
Only one header row. I attempted
worksheets("TradeHistory_20080920110258").range("a6"
,"o6").interior.color
Still have an error in the immediate window. Compile error.
worksheets("TradeHistory_20080920110258").range("a6"
,"o6").interior.color
Still have an error in the immediate window. Compile error.
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Parameters
1) Try
Dim TopLeft As Range
Set TopLeft = Range("A2").End(xlDown)
' Do something with TopLeft
Debug.Print TopLeft.Address
2) Try
Debug.Print Worksheets("TradeHistory_20080920110258").Range("A6").Interior.Color
Dim TopLeft As Range
Set TopLeft = Range("A2").End(xlDown)
' Do something with TopLeft
Debug.Print TopLeft.Address
2) Try
Debug.Print Worksheets("TradeHistory_20080920110258").Range("A6").Interior.Color
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
OK, then to set the cell to another color would be
TopLeft.Interior.Color = xyz ?
TopLeft.Interior.Color = xyz ?
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Parameters
Yes. For example:
TopLeft.Interior.Color = vbYellow
or
TopLeft.Interior.Color = RGB(255, 255, 0)
TopLeft.Interior.Color = vbYellow
or
TopLeft.Interior.Color = RGB(255, 255, 0)
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
All that works, now two more questions.
If I wanted to change the interior fill to a value of 13551615, what would that be? = 13551615 ?
Lastly if I wanted no fill = xlutomatic?A
If I wanted to change the interior fill to a value of 13551615, what would that be? = 13551615 ?
Lastly if I wanted no fill = xlutomatic?A
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Parameters
> If I wanted to change the interior fill to a value of 13551615, what would that be? = 13551615 ?
Yes, indeed
> Lastly if I wanted no fill = xlutomatic?A
Assuming that you want to set the background to No Fill, use
TopLeft.Interior.ColorIndex = xlColorIndexNone
Note the use if ColorIndex instead of Color here.
Yes, indeed
> Lastly if I wanted no fill = xlutomatic?A
Assuming that you want to set the background to No Fill, use
TopLeft.Interior.ColorIndex = xlColorIndexNone
Note the use if ColorIndex instead of Color here.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
Now a programing syntax?
what I have so far is
I won't know what row the TopLeft is but I want to reference cell D row of TopLeft. The code had an error at cel = Range("DArow") or rnage(DARow). so what is the best way to get around this?
what I have so far is
Code: Select all
Dim allrng As Range
Dim Arow As Range
'Dim dteToday As Date
'Dim dteTargetDate As Date
Set TopLeft = Range("A2").End(xlDown)
Set Arow = TopLeft.Rows
Set cel = Range("DArow")
-
- Administrator
- Posts: 12604
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Cell Parameters
How about using Range("D"&Arow) instead of Range ("DArow")
Alternatively you could use
Alternatively you could use
Code: Select all
Set Cel = Range("A2").End(xlDown).Offset(0,3)
StuartR
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
Yes, I tried the concatenate version but that gave similar error.StuartR wrote: ↑28 Nov 2021, 16:12How about using Range("D"&Arow) instead of Range ("DArow")
Alternatively you could useCode: Select all
Set Cel = Range("A2").End(xlDown).Offset(0,3)
The offset worked now it means move 0 rows and 3 columns left?
To move up would be -x?
To move left would be -y?
-
- Administrator
- Posts: 12604
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Cell Parameters
(0,3) = 0 rows down and 3 columns to the right
Yes, negative numbers would be up and left
Yes, negative numbers would be up and left
StuartR
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Parameters
StuartR's suggestion is the shortest, but an alternative would be
Code: Select all
Dim TopLeft As Range
Dim Arow As Long ' not Range
Dim cel As Range
Set TopLeft = Range("A2").End(xlDown)
Arow = TopLeft.Row ' nor Rows
Set cel = Range("D" & Arow) ' not "DArow"
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
I'm multitasking very poorly and have not tried
Set rng = Range(TopLeft, TopLeft.Offset(0, 13))
This in principle should work to define a range from TopLeft to include all cells out to column O?
Set rng = Range(TopLeft, TopLeft.Offset(0, 13))
This in principle should work to define a range from TopLeft to include all cells out to column O?
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
That did work.
Now I need to identify whether cel contains either *Close or *Open or;???Close or????Open.
Or do I have to code in a filtering?
Now I need to identify whether cel contains either *Close or *Open or;???Close or????Open.
Or do I have to code in a filtering?
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78478
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Cell Parameters
Code: Select all
If cel.Value Like "*Open" Then
...
ElseIf cel.Value Like "*Closed" Then
...
Else
' Neither
...
End If
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
Ok, the final code to set a new row with To Close will have no interior color, the new rows with To Open will have an interior color as the spreadsheet I shared with you.
Tanks to both.
Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TopLeft As Range
Dim cel As Range
Dim rng As Range
Set TopLeft = Range("A2").End(xlDown)
Set cel = Range("A2").End(xlDown).Offset(0, 3)
Set rng = Range(TopLeft, TopLeft.Offset(0, 13))
Application.ScreenUpdating = False
Application.EnableEvents = False
If cel.Value Like "*Open" Then
Set rng.Interior.Color = 13551615
ElseIf cel.Value Like "*Closed" Then
Set rng.Interior.ColorIndex = xlColorIndexNone
Else
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
The code fails at
Set rng.Interior.ColorIndex = xlColorIndexNone
Object required
Set rng.Interior.ColorIndex = xlColorIndexNone
Object required
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Cell Parameters
I don't understand but when I deleted the Set and It worked as desired. I did have to change the font color and that worked. I still don't understand the error as rng had been sat to a range.