copy cell formating to another sheet 1

[wmbb]

I want to ask the user to select a range and color this range in all sheets in the workbook.

I already have the following code...
It asks the user to select a range and colors the cells in the active sheet.
How do I color the cells in the other sheets ?
Can I copy the range from one worksheet to anaother worksheet ?

Set tspan = Application.InputBox("Select the first timespan in the upper device", "Time span", Type:=8)

tspan.Select

For Each Cell In Selection
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10211288
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Next

tspan.Copy

 

[SkipVought]

hi,
Same format for all sheets in workbook...

    Dim tspan, ws As Worksheet
    Set tspan = Application.InputBox("Select the first timespan in the upper device", "Time span", Type:=8)
    
    If Not tspan Is Nothing Then
        For Each ws In Worksheets
            With ws.Range(tspan.Address).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 10211288
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        Next
    End If

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wmbb]

Hi Skip,

Thank you for your response.
This piece of code is just a part of a bigger one.
I figured out that I can't solve the problem using your solution.
Because I have to move to other cells relative to the selected range in every sheet, I need to define the same range in all sheets as selected by the user in sheet1....

How do I define the same range in sheet2 as the user has selected in sheet1 ?

Thanks in advance.

 

[SkipVought]

How do I define the same range in sheet2 as the user has selected in sheet1 ?

????

That is EXACTLY what my code does!

If you have not defined your requiements completely, then how can a satisfactory solution be proposed? I solved the stated problem: 1) user defines a range, 2) apply a pre-set format to that range on every sheet. Is that not what you requested?

I have to move to other cells relative to the selected range in every sheet

What is the requirement for this -- SPECIFICALLY? Please define what this means for every sheet and we'll see if a suitable solution can be crafted.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wmbb]

Hi Skip,

You're right I was not clear enough in my question.
But your solution has sent me in the right direction and I have now solved the problem.
At first I thought that I had to define a named range in every sheet but now I see that this wasn't necessary.

Thanks for your help.

 

[PWD]

Well I'll give you a bloomin' star for that Skip!!

Many thanks,
D€$