Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cells in a column
The following code establishes "pages" to be printed from a sheet:
'Get the page 'identifiers' for this sheet Dim j As Long, rng1 As Range Dim Pagevarr() Dim rng As Range, Cell As Range ReDim Pagevarr(1 To 1) Set rng = Columns(1).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "Item" Then If IsEmpty(Pagevarr(1)) Then Set Pagevarr(1) = Cell Else ReDim Preserve Pagevarr(1 To UBound(Pagevarr) + 1) Set Pagevarr(UBound(Pagevarr)) = Cell End If End If Next ReDim Preserve Pagevarr(1 To UBound(Pagevarr) + 1) Set Pagevarr(UBound(Pagevarr)) = Cells(Rows.Count, £Col) _ .End(xlUp).Offset(1, -(£Col - 1)) ColsToPrint = LastCol 'Build an array of the ranges to be printed in this sheet ReDim Printvarr(1 To 1) For j = 1 To UBound(Pagevarr) - 1 'nb: change the Resize value to suit the number of columns 'to be printed Set rng1 = Range(Pagevarr(j), Pagevarr(j + 1).Offset _ (-1, 0)).Resize(, ColsToPrint) How can I amend this so that 'rng1' will only print out if there is a red coloured cell in column P, please? So if rng1 was "A20:O55", then only print if the range "P20:P55" contains one or more red cells. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.744 / Virus Database: 496 - Release Date: 24/08/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cells in a column
Set rng1 = Range(Pagevarr(j), Pagevarr(j + 1).Offset _
(-1, 0)).Resize(, ColsToPrint) rng2 = Intersect(rng1.EntireRow,Range("P1").EntireColumn) for each cell in rng2 if cell.interior.ColorIndex = 3 then rng1.printout exit for end if Next -- Regards, Tom Ogilvy "Stuart" wrote in message ... The following code establishes "pages" to be printed from a sheet: 'Get the page 'identifiers' for this sheet Dim j As Long, rng1 As Range Dim Pagevarr() Dim rng As Range, Cell As Range ReDim Pagevarr(1 To 1) Set rng = Columns(1).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "Item" Then If IsEmpty(Pagevarr(1)) Then Set Pagevarr(1) = Cell Else ReDim Preserve Pagevarr(1 To UBound(Pagevarr) + 1) Set Pagevarr(UBound(Pagevarr)) = Cell End If End If Next ReDim Preserve Pagevarr(1 To UBound(Pagevarr) + 1) Set Pagevarr(UBound(Pagevarr)) = Cells(Rows.Count, £Col) _ .End(xlUp).Offset(1, -(£Col - 1)) ColsToPrint = LastCol 'Build an array of the ranges to be printed in this sheet ReDim Printvarr(1 To 1) For j = 1 To UBound(Pagevarr) - 1 'nb: change the Resize value to suit the number of columns 'to be printed Set rng1 = Range(Pagevarr(j), Pagevarr(j + 1).Offset _ (-1, 0)).Resize(, ColsToPrint) How can I amend this so that 'rng1' will only print out if there is a red coloured cell in column P, please? So if rng1 was "A20:O55", then only print if the range "P20:P55" contains one or more red cells. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.744 / Virus Database: 496 - Release Date: 24/08/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check cells in a column
Many thanks.
Regards. "Tom Ogilvy" wrote in message ... Set rng1 = Range(Pagevarr(j), Pagevarr(j + 1).Offset _ (-1, 0)).Resize(, ColsToPrint) rng2 = Intersect(rng1.EntireRow,Range("P1").EntireColumn) for each cell in rng2 if cell.interior.ColorIndex = 3 then rng1.printout exit for end if Next -- Regards, Tom Ogilvy "Stuart" wrote in message ... The following code establishes "pages" to be printed from a sheet: 'Get the page 'identifiers' for this sheet Dim j As Long, rng1 As Range Dim Pagevarr() Dim rng As Range, Cell As Range ReDim Pagevarr(1 To 1) Set rng = Columns(1).SpecialCells(xlConstants, xlTextValues) For Each Cell In rng If Cell.Value = "Item" Then If IsEmpty(Pagevarr(1)) Then Set Pagevarr(1) = Cell Else ReDim Preserve Pagevarr(1 To UBound(Pagevarr) + 1) Set Pagevarr(UBound(Pagevarr)) = Cell End If End If Next ReDim Preserve Pagevarr(1 To UBound(Pagevarr) + 1) Set Pagevarr(UBound(Pagevarr)) = Cells(Rows.Count, £Col) _ .End(xlUp).Offset(1, -(£Col - 1)) ColsToPrint = LastCol 'Build an array of the ranges to be printed in this sheet ReDim Printvarr(1 To 1) For j = 1 To UBound(Pagevarr) - 1 'nb: change the Resize value to suit the number of columns 'to be printed Set rng1 = Range(Pagevarr(j), Pagevarr(j + 1).Offset _ (-1, 0)).Resize(, ColsToPrint) How can I amend this so that 'rng1' will only print out if there is a red coloured cell in column P, please? So if rng1 was "A20:O55", then only print if the range "P20:P55" contains one or more red cells. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.744 / Virus Database: 496 - Release Date: 24/08/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.744 / Virus Database: 496 - Release Date: 26/08/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display cells(text) in one column based on cells which are present inother column | Excel Discussion (Misc queries) | |||
How do I set up a column in Excel as a check mark column? | Excel Discussion (Misc queries) | |||
how to check for recurrence of same names in column 1 and column | Excel Discussion (Misc queries) | |||
How do I Make a column in excel a check box column | Excel Discussion (Misc queries) | |||
Check Box that Locks all cells on it's column | Excel Discussion (Misc queries) |