+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting using VBA - Code not working properly

  1. #1
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Conditional Formatting using VBA - Code not working properly

    Hi All,

    I have a VBA Code which sets Conditional Formatting for a range.

    But it's not working as it should be.

    Can anybody please have a look at attached file and suggest a solution.

    In the file, only one function is written as below:

    Please Login or Register  to view this content.
    Following is the conditional formatting rules in simple english:
    1. If Cell in column D is blank, no formatting should be applied.
    2. If Cell in column F is >= Cell in column E, that whole row should become green.


    Kindly help!
    Attached Files Attached Files
    Last edited by Tejas.T; 12-02-2012 at 05:57 PM.

  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
    44,086

    Re: Conditional Formatting using VBA - Code not working properly

    Please Login or Register  to view this content.

    Regards, TMS
    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


  3. #3
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Conditional Formatting using VBA - Code not working properly

    Hi TMS,

    It's really working now. WoW. Thanks a ton!

    What I was missing is
    Please Login or Register  to view this content.
    I think. Am I right?

    Also you have improved the format condition which is really great.

    Thanks,
    Tejas

  4. #4
    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
    44,086

    Re: Conditional Formatting using VBA - Code not working properly

    You're welcome. Thanks for the rep.

    Am I right?
    Pretty much. you need to be on the row where the Conditional Formatting starts. I suspect this could be considered a flaw but that's the way it is.

    And yes, the formula is improved. And I decided to clear only the cells used rather than the whole sheet though I'm not sure how much it improves efficiency. Excel probably knows which cells need clearing


    Regards, TMS

  5. #5
    Forum Contributor
    Join Date
    11-05-2012
    Location
    Montreal
    MS-Off Ver
    Excel 2007
    Posts
    107

    Re: Conditional Formatting using VBA - Code not working properly

    Hello Tejas,

    Is possible to attach your workbook with functioning vba?
    Plus, what is supposed to do exactly?

    Thank you

  6. #6
    Forum Contributor
    Join Date
    10-21-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    110

    Re: Conditional Formatting using VBA - Code not working properly

    @Naja:
    I have provided sample workbook and also explained what exactly needs to be done. Have a closed look.

    @TMShucks:
    I really liked the new formula you used. Thanks for that.
    Yes I saw that you are not clearing conditional formatting of whole sheet but I can't do that as my Range is dynamic every time.

  7. #7
    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
    44,086

    Re: Conditional Formatting using VBA - Code not working properly

    Please Login or Register  to view this content.

    ... will give you a dynamic range base on the number of rows in column C. It just means you're not trying to clear Conditional Formatting from cells where there is none. I suspect Excel is clever enough to know anyway but there might be a slight performance improvement by attacking the cells where you have data.


    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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