Consulting

Results 1 to 7 of 7

Thread: Conditional formatting question

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location

    Conditional formatting question

    Hello all,

    I have a spreadsheet that uses conditional formatting. The sheet is used by a lot of people and I never have any idea on how many rows will be used. Is there a way to conditional format down to only the last row used? Right now it is set up to 30,000 rows and it makes scrolling down a pain. Thanks
    Peace of mind is found in some of the strangest places.

  2. #2
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hi Austenr,

    It sounds to me like it isn't a problem with the conditional formats, but you may have a formula or something in those other cells.. conditional formats won't skew the scroll area.

    To see what I mean, create a new workbook, and place a conditional format in A1:A30000. The conditional format applies to those cells, but the scroll area is still only the used range.

    Matt

  3. #3
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Hi Matt,

    I have attached my conditional formatting formulas on the attached doc. What I wanted to do is have a way to detect the last row used and stop the formatting at that point. Or, option 2, have the user select the range. This sheet needs to be printed and the way it is now, it prints tons of nothing after the data ends.

    Peace of mind is found in some of the strangest places.

  4. #4
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    I see your attached word doc with the screen prints of the conditional formatting dialog, but I'm not sure what you want me to do? As I said before, you can have conditional formats on any cells you want, even 65536 rows x 256 columns, but it still won't change the print area or scroll area. Your issue of printing the blank pages at the end comes from something other than conditional formatting, can you possibly upload your workbook so I can take a look?

  5. #5
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    OK. Take a look at the attached workbook. As you can see the conditional formatting continues past the last row of data on each side (yellow and blue). Is there a way to detect the last row that has data on each side and cease the CF at that point?
    Peace of mind is found in some of the strangest places.

  6. #6
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    I do see what you mean, but those extra cells being printed aren't because of the conditional formatting, it is because of the border you have between G14:G16 and H14:H16.

    Remove the border between those cells, keeping the conditional formatting exactly as you have it. The cells stay yellow/blue, but they're not being printed because the used range of the page doesn't go there.

    If you want to get rid of the coloring, change the conditional format to ensure the cell isn't blank. For example, change this:
    =ISNA(VLOOKUP($B5,$I$5:$M$19605, 1,FALSE))

    To:
    =AND(ISNA(VLOOKUP($B5,$I$5:$M$19605, 1,FALSE)),$B5<>"")

    Matt

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by mvidas
    I do see what you mean, but those extra cells being printed aren't because of the conditional formatting, it is because of the border you have between G14:G16 and H14:H16.

    Remove the border between those cells, keeping the conditional formatting exactly as you have it. The cells stay yellow/blue, but they're not being printed because the used range of the page doesn't go there.
    Or even use CF tgo set those borders!

Posting Permissions

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