Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Format this like that

    I seem to recall a capability in one of those other spreadsheets (1-2-3 or Quattro) where one could specify the formatting for a cell be like that of another cell. I think you could do that as part of an IF test, which made it very powerful.

    I don't think I've seen anything like that in Excel - at least not in the normal capabilities. I've looked under custom formatting, conditional formatting, and probably a few other places.

    Any ideas?

    Fred

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format this like that

    You can copy the format from one cell to another, but that is done like copying the value, not as part of a formula. I've not heard of doing it in a formula.

    To copy formats, just copy the cell with the format you want to use (eg press Ctrl-C), and then go to the new cell and select Edit>Paste Special. Click on Formats and say OK.

    Jon

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format this like that

    You can do it with a macro, but not with standard formatting.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Format this like that

    Legare, Jon:

    I probably was not very clear in my original posting.

    I know that I can do a paste special w/ formats. But that is not dynamic.

    I can't even begin to imagine a macro for this. The capability for this that I recall seemed to be linked to the IF function. Not only could you specify the true and false parts but how each was to be formatted. But the format spec was not only static (like giving a custom format picture) but could be dynamic (refer to the format of another cell).

    So suppose I have a cell A1 with a value (however it gets it-from an IF, some formula, whatever). I want to format A1 to look like G10. If G10's format changes, so does A1's.

    Any ideas?

    Fred

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format this like that

    Fred,

    What about this code as a starting point:

    If Cells(5, 3).Value = 1 Then
    Cells(5, 3).Interior.ColorIndex = Cells(1, 1).Interior.ColorIndex
    End If
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Format this like that

    Geoff,

    Good starting point. Is there a way to copy all the formating of cell 1,1 to 1,3 w/o having to list each property separately?

    Also, how does one invoke that from an excel cell w/o running the macro? My recollection was that the other spreadsheet (whichever it was) treated this more like a function call.

    Fred

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format this like that

    Fred,

    a. Not that I know of. But that doesn't mean anything.
    b. You might put it in a WorkSheet Change event (In your VBE window, select the sheet you want to work on; click in the dropdown which says "(general)" and sleect "Worksheet", and select "Change" from the other)

    Geoff
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Format this like that

    Thanks Geoff.

    I'll work on it.

    On the issue of where to put it, I'd think I'd want this to be independent of a particular sheet. So wouldn't I want to put this as a Workbook SheetChange event?

    Fred

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Format this like that

    Hi,
    Your best bet for copying all the formatting would probably be to use .Copy and then .PasteSpecial - i.e. the code equivalent of what you would manually do. Saves you specifying each item.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format this like that

    Fred: You can use something like this in the Worksheet_Change event:

    <pre>With Worksheets("Sheet1")
    .Range("A1:A5").Copy
    .Range("D15").PasteSpecial Operation:=xlPasteFormats
    End With
    </pre>

    Legare Coleman

Posting Permissions

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