Posted to microsoft.public.excel.programming
|
|
Formatting data based on a cell range
Try replace
Range("A2:I??????")
with
Range("A2").currentregion
"tarns" wrote:
Hi Guys
I wanna be able to filter my set of data and then copy the autofilter
data to a new sheet and only apply formatting to those rows that
contain data. My function copies the data correctly but i dont know how
to pass the "RangeOfFilteredData" value into the subroutine, AND which
function to use to select a row range when i know the column range is
always 8, but the row range is always differing, eg the variable
"RangeOfFilteredData"
Code:
--------------------
sub blah()
Sheets("List").Select 'Contains the data
Selection.AutoFilter Field:=9, Criteria1:="<N/A" ' filtered data in sheet
Set NewOustandingRC = ActiveSheet.AutoFilter.Range ' copy fitered data
NewOustandingRC.Copy
RangeOfFilteredData = ActiveSheet.AutoFilter.Range
Sheets("Outstanding Issues").Select 'Paste data here
Range("A2").Select
Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Formatting(RangeOfFilteredData) ' <-Run Formatting Function
'UnFilter List in first sheet
Sheets("List").Select ' LIST
Selection.AutoFilter Field:=9
End Sub
Sub Formatting(Range As Integer)
Range("A2:I??????").Select <- I know the range
Selection.Interior.ColorIndex = 37
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub
--------------------
--
tarns
------------------------------------------------------------------------
tarns's Profile: http://www.excelforum.com/member.php...o&userid=32291
View this thread: http://www.excelforum.com/showthread...hreadid=520437
|