+ Reply to Thread
Results 1 to 2 of 2

Conditional Formatting, VBA and Paste Special

Hybrid View

  1. #1
    Registered User
    Join Date
    02-11-2011
    Location
    Las Cruces, NM
    MS-Off Ver
    Excel 2003
    Posts
    1

    Conditional Formatting, VBA and Paste Special

    I have a question about Paste Special.

    In general I have a column (K2:K160) that is conditionally formated to strike the text out if =not(isblank($L2). So this will go through the range K2 to K160 and check to see if L2 to L60, respectfully, has anything in it. If so it will strikeout the K columns cell.

    This works wonderfully

    I then have a VB script that is copying K2:K160 one by one and doing a paste special on others cells in this brick chart I have.

    The problem is that the paste special isn't copying the format of the K cell, it is copying the conditionally format formula from the K cell.

    example:
    L15 is empty.
    K60 is struck out because L60 has something in it.
    my VBA code copies K60 and does a paste special in B15.
    B15 is NOT struck out because the conditional format formula was copied instead and L15 doesn't have anything in it.
    *I just want B15 to have the absolure format of K60 which is conditionally set by L60.*

    Snippet of VBA code:
    Range("$k$69").Copy
    Range("$c$3").PasteSpecial xlPasteFormats
    Range("$k$211").Copy
    Range("$c$4").PasteSpecial xlPasteFormats
    Range("$k$89").Copy
    Range("$c$5").PasteSpecial xlPasteFormats
    Range("$k$39").Copy
    Range("$c$6").PasteSpecial xlPasteFormats
    Range("$k$40").Copy
    Range("$c$7").PasteSpecial xlPasteFormats
    Range("$k$41").Copy
    Range("$c$8").PasteSpecial xlPasteFormats
    ...

    Please help

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,506

    Re: Conditional Formatting, VBA and Paste Special

    The problem is that you have made the column reference absolute. This means that you still get
    =NOT(ISBLANK($L2)) as your criteria when you copy it to cell B2.

    Remove the $ fom the L column reference and you should be OK.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1