Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Conditional formatting question (Excel xp)

    I have one sheet in a workbook that has some formulas and then conditionally formats cells based on the results of the formula. I then wanted to paste the cells to another sheet along with those formats. For some reason i can paste the values but cannot get the formats to come with it. I am adding font bolding, italic and borders but they won't carry over to the 2nd sheet even if i paste special, and click on formats. What am i doing wrong here? Thank you.

  2. #2
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting question (Excel xp)

    Are you trying to paste regular formatting or conditional formatting? If you're pasting conditional formatting, you might want to check the cell references in your conditional formats after you've pasted them. They may not be referring to the cells you expect.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting question (Excel xp)

    hmmm, i see what u mean. I've attached a test. How do i copy A3:N3 from the stat sheet to the data sheet along with the borders and the italic and bold formatting? thanks

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting question (Excel xp)

    The formatting is being copied across, look at the formatting via Format Cells / Font and Borders, it's the same on both sheets.

    The conditional formatting can also be copied across using Paste, but then the CF on your data sheet refers to the cells on your data sheet, which do not fulfil the conditions. You will have to change the CF formulas to refer to your stat sheet to get it to work.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting question (Excel xp)

    i thought my CF formulas were referring to my stat sheet. I guess i don't understand.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting question (Excel xp)

    The formula for CF of H3 on your stat sheet refers to cells H1, H3, and H8 also on your stat sheet. The values in these cells fulfil the conditions, so H3 is formatted accordingly.

    The formula for CF of H3 on your data sheet refers to cells H1, H3 and H8 on your data sheet, H1 and H8 are blank on your data sheet so the conditions are not met and therefore H3 is not formatted the way you want. If you copy cells H1 and H8 from your stat sheet to H1 and H8 in your data sheet you will find H3 on your data sheet does become formatted the way you want.

    To do what you want, that is to capture the format of H3 and paste it somewhere else, effectively turning it from conditional formatting to 'straight' formatting is much more difficult, and I cannot see a way of doing it, though possibly via VBA...

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting question (Excel xp)

    ok, thank you very much for the help.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting question (Excel xp)

    OOps! sorry I did not see that H8 on your stat sheet is not a simple value, if you Paste Special / Values from H8 stat sheet to H8 data sheet, H3 data sheet becomes CF'd as you want.

    Also I found that conditional formats cannot refer to other worksheets or workbooks, therefore you cannot make the CF of H3 on the data sheet depend on H1 and H8 on the stat sheet (which would be messy anyway).

    This may need the attention of a VB Expert.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional formatting question (Excel xp)

    You can use values on other sheets if you NAME them first (insert name define) and then refer to the name.

    Naming ranges is a "good trick" to get around many of the "requirements" for being on the same sheet. Named ranges in many instances (not all) will make excel act like they are on the same sheet.

    Steve

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional formatting question (Excel xp)

    If you name the ranges (insert- name -define)
    Name: CompRow
    Refersto: =stat!$1:$1

    and
    Name: SigRow
    Refersto: =stat!$8:$8

    You can set the conditionl formatting formulas on data to:
    Condition 1:
    =AND(INDEX(SigRow,COLUMN(D8))="Sig",D3>=INDEX(Comp Row,COLUMN(D3)))

    Condition 2:
    =AND(INDEX(SigRow,COLUMN(D8))="Sig",D3<INDEX(CompR ow,COLUMN(D3)))

    And they will refer to the ranges in Stats

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting question (Excel xp)

    Very good point Steve.

Posting Permissions

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