range determination

[flamme20]

There's a macro in my workbook, here's an extract:

Range("K19:L30").Select
Selection.Copy

I copy this range and paste it two columns to the left

Range("G19:H30").Select
Selection.PasteSpecial ...etc.

The problem is that the range constantly changes, is there any way to determine the range without me having to check on it manually every time, say based on some principle (e.g. color)?

 

[Sthrncali]

Yep... Would need to know what "criteria" or "principle" determines the range...

i.e. What is the logic that will determine the range you want to use.

 

[wigi]

And also, do not .select.

This is equivalent:

Range("K19:L30").Copy

 

[wigi]

Sorry, I posted the same one twice.

 

[flamme20]

The range contains debit-credit values, and the issue is to copy the said range of a closing period to the range of the opening period at the beginning of every month (I've got criteria when the date of the month equals 1)

 

[wigi]

Please read back the contribution of Sthrncali and reply to it.
After that, helpers can try to write the code if your logic is coherent and clear.

 

[flamme20]

Yep... Would need to know what "criteria" or "principle" determines the range...

i.e. What is the logic that will determine the range you want to use.

Selection.Font.ColorIndex = 41

 

[Sthrncali]

You can certainly test if " Cell.Font.ColorIndex = 41", however this is not enough information to determine your range...

Would also need to know the scope... i.e.

Is it every cell on a worksheet that meets that criteria... Does that criteria define the left, right, upper, or lower bounds of your range... Is the range your trying to locate one cell, or a range of cells...

The more information your provide the better we are able to assist you...

Please clearly explain exactly what range you need the code to define...

I.E., Look in Row 1, between Col A and Z testing for Font.ColorIndex, when you find a match my range will be xyz relative to the match..

 

[flamme20]

You can certainly test if " Cell.Font.ColorIndex = 41", however this is not enough information to determine your range...

Would also need to know the scope... i.e.

Is it every cell on a worksheet that meets that criteria... Does that criteria define the left, right, upper, or lower bounds of your range... Is the range your trying to locate one cell, or a range of cells...

The more information your provide the better we are able to assist you...

Please clearly explain exactly what range you need the code to define...

I.E., Look in Row 1, between Col A and Z testing for Font.ColorIndex, when you find a match my range will be xyz relative to the match..

Let me try it this way:
If Column A contains "FY Trade" then look for the Range in Columns K & L based on Font Color with
Borders (xlEdgeTop)
.ColorIndex = xlAutomatic
.Weight = xlMedium
.LineStyle = xlContinuous
and
Borders (xlEdgeBottom)

In other words, would it be possible to define the range by top/bottom borders based on color criteria. Maybe even w/o column A containing a certain word?