Cell equals cell conditional formatting

[Jenawade]

I can't find the answer to this, and I'm sure it's something very simple for one of you excel guru's...

I have a spreadsheet with alot of cells set up to "=" a different cell on a different sheet (some of which are text and some are numbers) such as sheet2 F10 contains "=Sheet1!I32" I finally noticed that the "=" is only bringing over the data, and not the formatting of the original cells. If Sheet1 I32 text is blue, or, the text is white and the cell shaded blue, I want the same formatting in sheet2 F10.

Is there a simple solution to this?

Thanks for your time!
Jen

 

[DRJ]

Hi - Welcome to the board

There isn't really a simple solution. You can use VBA to change the formatting if you wanted. Why can't you just format the cells the same manually? i.e.

F10 = Sheet1!I32

Now just format F10 the same as Sheet1!I32

 

[Yesuslave]

Sub MirrorFormat()

'assign this macro to cntrl + q or some easy unused letter
'After you put in the =Cell Reference, simply hit your ctrl+q or whatever

Dim cellRef As String

cellRef = Mid(ActiveCell.Formula, 2, Len(ActiveCell.Formula))
ActiveCell.NumberFormat = Range(cellRef).NumberFormat
ActiveCell.Font.ColorIndex = Range(cellRef).Font.ColorIndex
ActiveCell.Interior.ColorIndex = Range(cellRef).Interior.ColorIndex
ActiveCell.Interior.Pattern = Range(cellRef).Interior.Pattern
ActiveCell.Font.Bold = Range(cellRef).Font.Bold

'and so on with each of the format types you would need


End Sub

 

[Jenawade]

I'm trying to cut out some of the manual work I have to do with a large monthly report that works its way up through the food chain. Data is generated on an internal website on my browser, then copied and pasted section by section to the first sheet of my spreadsheet so I have the layout the way I need it -- so it always falls in the right place on the following 4 sheets. Those 4 sheets reference the data on that first sheet, anywhere up to about 200 "=" references throughout each page, with everything lined up just as I need it for the meeting presentations, and all calculations done automatically. We have color coding to show if a supplier was under a special status, such as on the Top Focus program, has become a Chronic Offender, will be De-Sourced, etc. If there's a lot of color, your report is fantastic -- don't ask me -- so we have to do this every month. And the data, and therefore the color, of different suppliers is different every month. Right now I have it down to copying/pasting and then changing all my colors manually, and I'm hoping to be able to just alter the color on the first sheet where all the data just goes straight down a few columns, and have the following sheets adjust automatically.

 

[Yesuslave]

Ok, here is an untested macro to do a whole sheet at a time all you have to do is put the cursor on the rightmost and bottom most cell you want tested. Thus, if you data goes all the way to column I in some places, and as low as row 300, then you'd put your cursor on I300:


Option Explicit

Sub MirrorFormat()

Dim cellRef As String
Dim EndRow As String
Dim intcol As Integer


EndRow = ActiveCell.Row + 1

intcol = ActiveCell.Column

Range("A1").Select
Do Until ActiveCell.Row = EndRow
Do Until ActiveCell.Column = intcol + 1

If Mid(ActiveCell.Formula, 1, 1) = "=" Then
On Error GoTo NotValid

cellRef = Mid(ActiveCell.Formula, 2, Len(ActiveCell.Formula))
ActiveCell.NumberFormat = Range(cellRef).NumberFormat
ActiveCell.Font.ColorIndex = Range(cellRef).Font.ColorIndex
ActiveCell.Interior.ColorIndex = Range(cellRef).Interior.ColorIndex
ActiveCell.Interior.Pattern = Range(cellRef).Interior.Pattern
ActiveCell.Font.Bold = Range(cellRef).Font.Bold
End If

NotValid:
ActiveCell.Offset(0, 1).Select
Loop
Range("A" & ActiveCell.Row + 1).Select
Loop



'and so on with each of the format types you would need


End Sub

 

[Tom Urtis]

Jen - -

Make a copy of your workbook to test something.

Go to Sheet2 or Sheet3 or wherever you have these formulas pointing to Sheet1. If that sheet is protected, unprotect it.

Note, this will not work if Sheet1 cells are conditionally formatted. You would need to copy the FormatConditions of the Sheet1 cells. I assumed (maybe incorrectly) that the Sheet1 cells are color shaded and bolded, etc, by what you did from the Format > Cells > Patterns tab. If you used CF instead, this macro will not work.

If you *did* change the actual cell properties of Sheet1 by manually putting color and bold formatting in them, then run this macro:

Sub Test1()
With Application
.ScreenUpdating = False
Dim X As Integer, Y As Range, Z As String
For Each Y In Cells.SpecialCells(-4123, 23)
If Left(Y.Formula, 8) = "=Sheet1!" Then
X = InStr(1, Y.Formula, "!")
Z = Mid(Y.Formula, X + 1)
Sheets("Sheet1").Range(Z).Copy: Y.PasteSpecial Paste:=xlFormats
End If
Next Y
.CutCopyMode = False
.ScreenUpdating = True
End With
End Sub


Whatcha think?

 

[Jenawade]

Ok, here is an untested macro to do a whole sheet at a time all you have to do is put the cursor on the rightmost and bottom most cell you want tested.

Stunning! Marvelous! A work of art! ray:

:wink:

Thank you soooo much! This is working perfectly and is going to save me so much time every month!!


Jen

 

[Yesuslave]

Glad to help