+ Reply to Thread
Results 1 to 7 of 7

if blank cells, paint yellow. if cell to left is less than a number, don't paint

  1. #1
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    if blank cells, paint yellow. if cell to left is less than a number, don't paint

    Hi,
    I have a spreadsheet which contains 39 columns with a header row. The number of rows varies per spreadsheet. I'm running into a problem with highlighting(painting yellow) cells. Really the problem is selecting the cells to be highlighted. The macro is skipping some of my code completely and in other cases leaving some cells unhighlighted.

    1. All blank cells in row AG should be painted yellow.

    2. If column AF contains a cell with number 900 or greater, then the cell in AG (same row) should NOT be painted yellow.

    3. All blank cells in rows AC and G should be painted yellow. Some spreadsheets may not contain blank cells.

    Problem with the code: VB is not performing the code after the first command. It will highlight MOST of the cells in AG but for some reason it is leaving a few blank ones unhighlighted. The blank ones are usually at the bottom of the rows. I have no idea why it would miss these.

    I believe the "ucase" tag is incorrect since its looking for a number but I am not sure what to put in its place and if I use something else should I dim it as ""?


    Can anyone help? thanks!

    I've attached an excel spreadsheet which contains a sample to this problem. I've also attached what it should look like if my code worked.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by curbster; 09-08-2009 at 07:25 PM.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,854

    Re: if blank cells, paint yellow. if cell to left is less than a number, don't paint

    Hi Curbster, try this updated code:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: if blank cells, paint yellow. if cell to left is less than a number, don't paint

    Paul thank you for your help! This code works great on that example. I am running into a problem though because the code after it has a loop. How do I protect this code from not looping more than once?

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,854

    Re: if blank cells, paint yellow. if cell to left is less than a number, don't paint

    Not 100% sure I understand, but you could probably wrap all of the code I provided in an IF statement.

    Basically, at the start of your main code, DIM a boolean variable. At the end of my code (before the End Sub) set that variable to True. Use an IF at the beginning to check if that variable is True before running, like so:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: if blank cells, paint yellow. if cell to left is less than a number, don't paint

    see message below.
    Last edited by curbster; 09-08-2009 at 07:18 PM.

  6. #6
    Registered User
    Join Date
    08-11-2009
    Location
    Gilbert, Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    82

    Re: if blank cells, paint yellow. if cell to left is less than a number, don't paint

    Paul, it was the table that was messing everything up. Your code worked near perfect, I just had to move it to before the table was created in my pages of code.

    However, it is still highlighting all blank cells in column AG whether or not the prior cell was <900. It looks like it only leaves the first 900 unhighlighted, and then goes on to the next.

    Any ideas?

    Thank you again for your help!

    UPDATE: I saved the file, closed it and reopened it, and it works like a charm. How wierd. Anyway, problem solved. Thanks Paul!
    Last edited by curbster; 09-08-2009 at 07:24 PM.

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: if blank cells, paint yellow. if cell to left is less than a number, don't paint

    Why aren't you using Conditional Formatting?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ 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