VBA and Conditional Formatting

[Stiner]

Hi there,

I have 2 questions.

First off let me explain what I am currently accomplishing and what I would like to further accomplish...

I have 9 spreadsheets (which act as working documents for 9 users) each user enters data their personal excel sheets and enters data under the corresponding headings. They then click Submit (the submit button is a Command Button which is one of the selections under the ActiveX controls) I have configured this button to transfer the data over to the Master Spreadsheet, Save and Close the master (to eliminate multiple people from being in the master at once).

I now am looking to conditional format the data from the 9 working spreadsheets ones it is moved over to the master - I do not want it to deletethe data just yet - I just want it to be conditional formatted with a red background for example so that users know that it has been moved.

I also want to moved multiple lines at once from the user workbooks into the master - currently I can only move 1 line at a time (I understand this is because I have specified ("A2") however I would like to incorporate the ability to move as many as 10 records at a time. I have tried to change my code to include ranges ("A2":"A11") however this does not work and gives me an error...

Anyway... here is my code (if anyone could please help me I would REALLY appreciate it!):

Option Explicit


Private Sub CommandButton1_Click()
Dim ID As Variant
Dim DateLogged As Variant
Dim Detectedby As Variant
Dim TestScriptIDCutoverTaskDescription As Variant
Dim StepNo As Variant
Dim DefectSummary As Variant
Dim DefectDetails As Variant
Dim Module As Variant
Dim OwningTeamLead As Variant
Dim Origin As Variant
Dim Severity As Variant
Dim Status As Variant
Dim Assignedto As Variant
Dim TargetCompletionDate As Variant
Dim ResolutionDetails As Variant
Dim Resolutiondate As Variant
Dim OSSNote As Variant
Dim OSSNoteImpactAnalysis
Dim Transport As Variant
Dim Disposition As Variant
Dim PDD As Variant
Dim ConfigDoc As Variant
Dim UnitTestScript As Variant
Dim SITScript As Variant
Dim ConversionPlan As Variant
Dim Aging As Variant
Dim RowCount As Variant
Dim ColumnCount As Variant
Dim x1Down As Variant
Dim COPYOFDEFECTLOG As Workbook


Worksheets("Defect Log").Select
ID = Range("A2:A11")
Worksheets("Defect Log").Select
DateLogged = Range("B2:B11")
Worksheets("Defect Log").Select
Detectedby = Range("C2:C11")
Worksheets("Defect Log").Select
TestScriptIDCutoverTaskDescription = Range("D2:D11")
Worksheets("Defect Log").Select
StepNo = Range("E2:E11")
Worksheets("Defect Log").Select
DefectSummary = Range("F2:F11")
Worksheets("Defect Log").Select
DefectDetails = Range("G2:G11")
Worksheets("Defect Log").Select
Module = Range("H2:H11")
Worksheets("Defect Log").Select
OwningTeamLead = Range("I2:I11")
Worksheets("Defect Log").Select
Origin = Range("J2:J11")
Worksheets("Defect Log").Select
Severity = Range("K2:K11")
Worksheets("Defect Log").Select
Status = Range("L2:L11")
Worksheets("Defect Log").Select
Assignedto = Range("M2:M11")
Worksheets("Defect Log").Select
TargetCompletionDate = Range("N2:N11")
Worksheets("Defect Log").Select
ResolutionDetails = Range("O2:O11")
Worksheets("Defect Log").Select
Resolutiondate = Range("P2:P11")
Worksheets("Defect Log").Select
OSSNote = Range("Q2:Q11")
Worksheets("Defect Log").Select
OSSNoteImpactAnalysis = Range("R2:R11")
Worksheets("Defect Log").Select
Transport = Range("S2:S11")
Worksheets("Defect Log").Select
Disposition = Range("T2:T11")
Worksheets("Defect Log").Select
PDD = Range("U2:U11")
Worksheets("Defect Log").Select
ConfigDoc = Range("V2:V11")
Worksheets("Defect Log").Select
UnitTestScript = Range("W2:W11")
Worksheets("Defect Log").Select
SITScript = Range("X2:X11")
Worksheets("Defect Log").Select
ConversionPlan = Range("Y2:Y11")
Worksheets("Defect Log").Select
Aging = Range("Z2:Z11")


Set COPYOFDEFECTLOG = Workbooks.Open("LINK")
Worksheets("Defect Log").Select
Worksheets("Defect Log").Range("A1").Select
RowCount = Worksheets("Defect Log").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Defect Log").Range("A1")
.Offset(RowCount, 0) = ID
.Offset(RowCount, 1) = DateLogged
.Offset(RowCount, 2) = Detectedby
.Offset(RowCount, 3) = TestScriptIDCutoverTaskDescription
.Offset(RowCount, 4) = StepNo
.Offset(RowCount, 5) = DefectSummary
.Offset(RowCount, 6) = DefectDetails
.Offset(RowCount, 7) = Module
.Offset(RowCount, 8) = OwningTeamLead
.Offset(RowCount, 9) = Origin
.Offset(RowCount, 10) = Severity
.Offset(RowCount, 11) = Status
.Offset(RowCount, 12) = Assignedto
.Offset(RowCount, 13) = TargetCompletionDate
.Offset(RowCount, 14) = ResolutionDetails
.Offset(RowCount, 15) = Resolutiondate
.Offset(RowCount, 16) = OSSNote
.Offset(RowCount, 17) = OSSNoteImpactAnalysis
.Offset(RowCount, 18) = Transport
.Offset(RowCount, 19) = Disposition
.Offset(RowCount, 20) = PDD
.Offset(RowCount, 21) = ConfigDoc
.Offset(RowCount, 22) = UnitTestScript
.Offset(RowCount, 23) = SITScript
.Offset(RowCount, 24) = ConversionPlan
.Offset(RowCount, 25) = Aging


End With
COPYOFDEFECTLOG.Save
COPYOFDEFECTLOG.Close
End Sub