The same format on different worksheets 4

[Boomerang]

I have 2 worksheets named "first" and "second"

Worksheet "first" has only text.
Worksheet "second" has formula's to copy the text from "first" like:

='first'!A4
='first'!K13

I change the text in "A4" on "first" then the text in "second-A4" change like the way I want.

But if I change the backgroundcolor[color] in "A4" on "first" then the backgroundcolor in "second-A4" don't change.

Is it possible that the format's on worksheet "second" change if I change the formats on worksheet "first" ???

Please help,
Erik
<-- My sport: Boomerang throwing !!
!! Many Happy Returns !! -->

 

[xlbo]

you'll need code for that:
Right click on sheet &quot;first&quot; tab
choose &quot;View Code&quot;
From the right hand combo box, choose &quot;Change&quot;
Delete the &quot;selection change&quot; sub which will have been automatically generated

Enter this in the change sub:

sheets(&quot;Second&quot.range(target.address).interior.colorindex = target.interior.colorindex


watch for word wrap Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[SkipVought]

Hi,
You know that if you Group sheets, whatever you do on the Active Sheet is duplicated on all the sheets in the group. Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Boomerang]

Geoff, I can't get it to work:

This is what I have (I have a Dutch version, and this is the real name of the sheet)

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(&quot;overzicht-CWI-GAK&quot.Range(Target.Address).Interior.ColorIndex = Target.Interior.ColorIndex
End Sub

Skip,

I don't understand what you mean? How can i Group sheets???

Erik <-- My sport: Boomerang throwing !!
!! Many Happy Returns !! -->

 

[Boomerang]

Skip,

I don't want everything duplicated on the &quot;second&quot; sheet.
Only a part of the &quot;first&quot; sheet.

Erik <-- My sport: Boomerang throwing !!
!! Many Happy Returns !! -->

 

[DaleWatson123321]

Erik,

I believe you'll be interested in a relatively unknown &quot;feature&quot; of Excel called the &quot;Camera&quot;. Using the Camera, one can take pictures, and place the pictures on separate sheets.

The IMPORTANT point, is that the pictures are &quot;linked&quot; to the original data. Therefore any changes you make to the original, will be automatically reflected in the picture. This includes ALL changes... formatting, changes in data, insertion/deletion of rows/columns, etc.

To activate the &quot;Camera&quot;, you can... Hold down the <Control> and (left) <Shift> key, while you click on &quot;Edit&quot; (on the menu), and then click on &quot;Copy Picture&quot;.

There is a &quot;Camera&quot; icon that you can place on one of your existing toolbars.  These are the steps involved.

1) From the menu, choose:  Tools - Customize

2) Click the &quot;Commands&quot; tab.

3) Under &quot;Categories&quot; (left side), click on &quot;Tools&quot;.

4) Under &quot;Commands (right side), scroll down until you find the &quot;Camera&quot; icon - it's near the bottom.

5) Click-and-drag the icon to one of your existing toolbars.

In using the Camera...

a) Highlight a range (one range at a time),

b) Click the &quot;Camera&quot; icon.  This will cause the mouse-pointer to change to a plus character... +

c) Click on a separate sheet (create a new sheet ahead of time if required).

d) Left-click to drop the picture.  Once you've dropped it, you can easily move it around.

And a &quot;bonus&quot;...  If desired, you can easily &quot;enlarge&quot; the picture - by clicking-and-dragging a &quot;corner&quot; handle.  And you can &quot;stretch&quot; the picture - by clicking-and-dragging one of the &quot;middle&quot; handles.

Note...  If you have assignd range names to the individual ranges, you can use these range names with your pictures.  While clicked on the picture, simply type: =xxx   ...where you replace &quot;xxx&quot; with your range name, and <Enter>.

I hope you find this useful for your current situation. If not, then I hope you'll be able to make use of this in other situations. Please advise as how how you make out with your &quot;new photography skills&quot;.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

Can you clarify a point? Is it correct that the cells on the &quot;overzicht-CWI-GAK&quot; sheet reference the exact same row and column on the &quot;first&quot; sheet? That is to say, from the point of view of the &quot;second&quot; sheet, are these the formulae?

A4: ='first'!A4
K13: ='first'!K13

or is the &quot;second&quot; sheet some sort of recap that is pulling data from various cells in the first sheet?

 

[Boomerang]

Zathras,

Yes they are,

A4: ='first'!A4
K13: ='first'!K13

Except for the last column, but I can easily correct that by adding a hidden column.

Why are you asking this ??

Erik <-- My sport: Boomerang throwing !!
!! Many Happy Returns !! -->

 

[Zathras]

Because the previous suggestions were making that assumption and I just wanted to be sure we weren't going down the wrong path.

 

[Boomerang]

Can somebody tell me why XLBO's example is not working???

Maybe somebody can send me a file with this example working???

erik.leferink@wanadoo.nl

PLEASE HELP !!!
<-- My sport: Boomerang throwing !!
!! Many Happy Returns !! -->

 

[Zathras]

Because the Change event isn't fired for a background color change, only for a data change.

 

[SkipVought]

Because you can't reference another worksheet from a Worksheet object. You must use a separate Module...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    Set rng = Application.Intersect(Target, Range("ColRng"))
    If Not rng Is Nothing Then
       For Each r In rng
          DupCell r
       Next
    End If
End Sub

In a separate Module
Sub DupCell(rng As Range)
    With rng
        With Sheets(2).Cells(.Row, .Column)
            .Value = rng.Value
            .Interior.ColorIndex = rng.Interior.ColorIndex
        End With
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Boomerang]

Skip,

I'm sorry but I can't get your example to work :-(
I'm not realy good with VB, modules etc.

Can you send me a file with a working example?

PLEASE !!!!

erik.leferink@wanadoo.nl

Erik <-- My sport: Boomerang throwing !!
!! Many Happy Returns !! -->

 

[SkipVought]

On its way.... Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Here is some code that should work for you:
Put this in the &quot;first&quot; sheet code page:

Option Explicit
Private Sub Worksheet_Deactivate()
  TransferColor
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  TransferColor
  If Target.Cells.Count = 1 Then
    SaveAddress = Target.Address
  End If
End Sub

And put this in a separate code module:

Option Explicit
Public SaveAddress As String
Const MASTER_SHEET = "first"
Const SLAVE_SHEET = "second"

Sub TransferColor()

' If the cell on the SLAVE_SHEET has a formula
' that references a cell on MASTER_SHEET, then
' the interior color is updated.

Dim rSourceRange As Range
Dim rRangeToUpdate As Range
Dim rCellToUpdate As Range
  If SaveAddress <> &quot;&quot; Then
    With Sheets(MASTER_SHEET)
      Set rSourceRange = .Range(SaveAddress)
    End With
    With Sheets(SLAVE_SHEET)
      On Error Resume Next
      Set rCellToUpdate = .Range(SaveAddress)
      If Not rCellToUpdate Is Nothing Then
        If rCellToUpdate.HasFormula Then
          If InStr(1, rCellToUpdate.Formula, MASTER_SHEET) Then
            rCellToUpdate.Interior.ColorIndex = rSourceRange.Interior.ColorIndex
          End If
        End If
      End If
    End With
    Set rSourceRange = Nothing
    Set rRangeToUpdate = Nothing
    Set rCellToUpdate = Nothing
  End If
End Sub

Change the values in the sheet name constants to reflect the actual sheet names you are using, and you should be set to go.

 

[Boomerang]

Zathras,

Private Sub Worksheet_Deactivate()
TransferColor
End Sub

Gives a Compilation-error:
Sub of function is not defined

Erik <-- My sport: Boomerang throwing !!
!! Many Happy Returns !! -->

 

[Zathras]

Did you follow my instructions to put that part of the code in a separate code module? (Insert/Module from the menu.)

 

[Boomerang]

Zathras, Skip, Dale,

I placed the module at the wrong place (not in the VBA-project)

I got all your (3) examples working now.
Thanx a lot, you where all three a very kind and very skilled help !!!

xlbo, I didn't got your example to work but I star you because of your very quick post, trying to help me.

Keep on doing the good job, guys.

Dutch greetings from Holland,
Erik

<-- My sport: Boomerang throwing !!
!! Many Happy Returns !! -->

 

[Boomerang]

Zathras,

PLEASE HELP AGAIN !!

I used your example, but I noticed that I CAN NOT copy and past anymore in the master-sheet ???

If I ignore the macro's when I open the file the copy and past posibility in the master-sheet is back to normal ?????

Please help,
Erik

<-- My sport: Boomerang throwing !!
!! Many Happy Returns !! -->

 

[Zathras]

That's a strange one. I would not have expected it. But in any case, change the functions to include a test for whether you are in copy/paste mode and only do the color check when not copying a cell:

Option Explicit

Private Sub Worksheet_Deactivate()
  If Application.CutCopyMode = False Then
    TransferColor
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
  If Application.CutCopyMode = False Then
    TransferColor
    If Target.Cells.Count = 1 Then
      SaveAddress = Target.Address
    End If
  End If
End Sub

If you change the cell color and then do a copy/paste and then change the cell color of the cell where you pasted, the colors may not set up correctly in the second sheet. Best if you only do one thing at a time. If you really need to handle that complexity and find that there is a problem, let me know and I can probably come up with another fix, but it will have to wait until tomorrow. I'm gone.