Set formatting variable

[Sige]

Hi All,

Can I set Range A1:H2 with Formatting:....
But if Usedrange.column > H then Set Formatting
on Range:A1:Usedrange.column2

if usedrange.column<H then keep range A1:H2

Brgds Sige

 

[Norman Jones]

Hi Sige,

The following does, I think, what you ask. However, this may well not be
what you intend.

Perhaps, therefore, it might be worthwhile explaining what it is that you
are trying to achieve and, possibly, why.

My code may well be susceptible to significant simplification, but my
incertitude as to your aims precludes me from investigation,

'=============>>
Public Sub Tester()
Dim rng1 As Range
Dim rng2 As Range
Dim LastCol As Long
Dim lastRow As Long

Set rng1 = ActiveSheet.UsedRange
With rng1
LastCol = .Columns(.Columns.Count).Column

lastRow = .Rows.Count - .Row + 1
End With

If LastCol > 8 Then
Set rng2 = Range("A1", _
Cells(lastRow, rng1.Columns(2).Column))
Else
Set rng2 = Range("A1:H2")
End If
With rng2
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
End With
End Sub
'<<=============

 

[Sige]

Hi Norman,

The black cells I am coloring should indicate how wide (wide) the last
column is going of my UsedRange ... always coloring row 1 & 2.(titles
will be written in here)

I think the code should be as follows:

If LastCol > 8 Then
Set rng2 = Range("A1", Cells(2, LastCol))

The thing is that the UsedRange can change while working on the sheet
.... Worksheet_SelectionChange might be a solution to update this
behaviour...& Also ressetting the UsedRange.
I am afraid though that this will invoke emptying the Undo-list!

Way around?
Brgds Sige

 

[Sige]

Hi Norman,

Hmmm... resetting last cell (column) ... once columns are colored they
will be part of the used range. So can only expand to right it
seems...

Unless the usedrange gets evaluated not taking into account row 1&2.

It will serve its purpose like this I guess.

Sige

 

[Norman Jones]

Hi Sige,

I think the code should be as follows:

If LastCol > 8 Then
Set rng2 = Range("A1", Cells(2, LastCol))

The thing is that the UsedRange can change while working on the sheet
... Worksheet_SelectionChange might be a solution to update this
behaviour...& Also ressetting the UsedRange.
I am afraid though that this will invoke emptying the Undo-list!

If your purpose is to set the black background / white font for rows 1:2,
why bother to monitor the number of columns in the used range?

In the absence of definitive information, make an informed guess:

With Range("A1:Z2")
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
End With