Results 1 to 8 of 8

Cell equals cell conditional formatting

This is a discussion on Cell equals cell conditional formatting within the Excel Questions forums, part of the Question Forums category; I can't find the answer to this, and I'm sure it's something very simple for one of you excel guru's... ...

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Michigan USA
    Posts
    227

    Default Cell equals cell conditional formatting

    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

  2. #2
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    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

  3. #3
    Board Regular
    Join Date
    Sep 2003
    Location
    New Jersey
    Posts
    297

    Default

    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
    Microsoft Office/VB/VB.Net Consulting for reasonable prices. Website coming soon! For inquiries contact Joshua at iesu_doulos@yahoo.com or by phone at (609)-440-9498

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Michigan USA
    Posts
    227

    Default Re: Cell equals cell conditional formatting

    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.

  5. #5
    Board Regular
    Join Date
    Sep 2003
    Location
    New Jersey
    Posts
    297

    Default Re: Cell equals cell conditional formatting

    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
    Microsoft Office/VB/VB.Net Consulting for reasonable prices. Website coming soon! For inquiries contact Joshua at iesu_doulos@yahoo.com or by phone at (609)-440-9498

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default Re: Cell equals cell conditional formatting

    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?

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Michigan USA
    Posts
    227

    Default Re: Cell equals cell conditional formatting

    Quote Originally Posted by 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.
    Stunning! Marvelous! A work of art!



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


    Jen

  8. #8
    Board Regular
    Join Date
    Sep 2003
    Location
    New Jersey
    Posts
    297

    Default Re: Cell equals cell conditional formatting

    Glad to help
    Microsoft Office/VB/VB.Net Consulting for reasonable prices. Website coming soon! For inquiries contact Joshua at iesu_doulos@yahoo.com or by phone at (609)-440-9498

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com