Automatic copy of cell formatting between sheets
 

Hi,

I've got this follow-up question buried in another post (Selective
selecting of multiple tabs), but I felt I should pull it out here as a
seperate post in hopes of getting more views.

I recieved some great help that led to a positive solution (solution
from Jim Cone included below), and I'm hoping to be able to tweak this
macro so that the formatting in the cell on the CTD sheet matches that
of the same cell on the Data Entry sheet. When data gets entered onto
the Data Entry sheet, we set significant figures and cell orientation
(center or right justified). Is there a way for that to get
translated to the CTD tab too? Thanks.

Frank

On Fri, 15 Apr 2011 20:05:33 -0700, "Jim Cone"
wrote:

It is good practice to use Option Explicit and declare all variables.
In worksheet event code "Target" is the active cell.
Qualify all ranges with the parent sheet (and the workbook if there is more than one).
The following code is untested but should be close to what you want...
'---
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myActiveCell As String
Dim myFormula As String

myActiveCell = Target(1).Address(False, False)
myFormula = Sheets("CTD").Range(myActiveCell).FormulaR1C1
If InStr(1, myFormula, "=IF(ISERROR", vbTextCompare) Then
Sheets("Data Entry").Select
Else
Sheets(Array("Data Entry", "CTD")).Select
End If
End Sub
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Determine Colors (excel add-in) - in the free folder)


on 4/15/2011, Phrank supposed :
Hi,

I've got a workbook with one tab for data entry and the other tab for
data presentation (with imbedded formulas for %differences from
controls and such). Initially, both sheets are identical, but I
sometimes need to insert rows and columns. Rather than doing this
twice, I just select both tabs. The problem is that I (and others)
oftentimes forget to have both tabs selected during modification or
data entry, and we end up having to waste time fixing things. So, I
drafted a quick macro for the Worksheet_SelectionChange event that I
thought would work well, but I've got a snag. It reads the address of
the active cell, then checks the same cell in the 2nd sheet for the
formula. IF the formula starts a certain way (with =IF(ISERRO), then
I want the macro to end. If it doesn't start with that, then I want
the macro to force the selection of the second tab. But, it's failing
when it tries to check the same cell on the 2nd sheet. The macro
seems to work if I try it outside of the event routine, but when I put
it in the Worksheet, it fails. Can anyone give me advice on how to
fix and/or improve this please? Thanks.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
myActiveCell = ActiveCell.Address
Sheets("CTD").Activate
Range(myActiveCell).Activate ' IT'S FAILING RIGHT HERE. WHY?
myFormula = ActiveCell.FormulaR1C1
myStartFormula = Mid(myFormula, 1, 11)
If myStartFormula = "=IF(ISERROR" Then
Sheets("Data Entry").Select
GoTo Line1
Else

Sheets(Array("Data Entry", "CTD")).Select

End If

Line1:
End Sub