Printable View
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 :think:
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
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.
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?
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?
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
Or even use CF tgo set those borders!Quote:
Originally Posted by mvidas