Cell Parameters

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Cell Parameters

Post by bknight »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cell Parameters

Post by HansV »

1) How many header rows do you have?

2) To get the fill color, use Range(...).Interior.Color
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by bknight »

Only one header row. I attempted

worksheets("TradeHistory_20080920110258").range("a6"
,"o6").interior.color
Still have an error in the immediate window. Compile error.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cell Parameters

Post by HansV »

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
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by bknight »

OK, then to set the cell to another color would be
TopLeft.Interior.Color = xyz ?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cell Parameters

Post by HansV »

Yes. For example:

TopLeft.Interior.Color = vbYellow

or

TopLeft.Interior.Color = RGB(255, 255, 0)
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by bknight »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cell Parameters

Post by HansV »

> 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.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by bknight »

Now a programing syntax?
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")
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?

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Cell Parameters

Post by StuartR »

How about using Range("D"&Arow) instead of Range ("DArow")
Alternatively you could use

Code: Select all

 Set Cel = Range("A2").End(xlDown).Offset(0,3)
 
StuartR


bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by bknight »

StuartR wrote:
28 Nov 2021, 16:12
How about using Range("D"&Arow) instead of Range ("DArow")
Alternatively you could use

Code: Select all

 Set Cel = Range("A2").End(xlDown).Offset(0,3)
 
Yes, I tried the concatenate version but that gave similar error.
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?

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Cell Parameters

Post by StuartR »

(0,3) = 0 rows down and 3 columns to the right
Yes, negative numbers would be up and left
StuartR


User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cell Parameters

Post by HansV »

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

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by bknight »

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?

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by bknight »

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?

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cell Parameters

Post by HansV »

bknight wrote:
28 Nov 2021, 16:58
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?
Yes.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Cell Parameters

Post by HansV »

bknight wrote:
28 Nov 2021, 18:28
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?

Code: Select all

    If cel.Value Like "*Open" Then
        ...
    ElseIf cel.Value Like "*Closed" Then
        ...
    Else
        ' Neither
        ...
    End If
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by bknight »

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.

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
Tanks to both.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by bknight »

The code fails at
Set rng.Interior.ColorIndex = xlColorIndexNone

Object required

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Cell Parameters

Post by bknight »

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.