YellowLabPro
09-20-2006, 01:47 PM
The following code changes the value in column A to blank if the value in column C is larger than 998.
I would like to change the value in the whole row to blank if the value in column C is larger than 998.
The line is the last line of code, how do I change this?
Thanks,
YLP
Sub PriceLabels()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LRow1 As Long, LRow2 As Long
Dim r As Long
Set ws1 = Sheets("Update")
Set ws2 = Sheets("PriceLabels")
LRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
LRow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws2.Activate
ws2.Range("a2:d" & LRow2).Clear
ws2.Range("A1:D1") = Array("Item#", "Record Description", "Qty", "Price")
ws1.Range("A2:B" & LRow1).Copy ws2.Range("A2")
ws1.Range("G2:G" & LRow1).Copy ws2.Range("C2")
ws1.Range("L2:L" & LRow1).Copy ws2.Range("D2")
With ws2
.Rows("1:1").HorizontalAlignment = xlCenter
.Rows("1:1").Font.Bold = True
.Cells.Columns.AutoFit
.Rows("1:1").HorizontalAlignment = xlCenter
.Rows("1:1").Font.Bold = True
End With
With ws2.Range("A2:D" & LRow2)
.Select
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC3>998"
.FormatConditions(1).Interior.ColorIndex = 1
End With
For r = 2 To LRow2
If Cells(r, "C").Value > 998 Then Cells(r, "A").Value = ""
Next r
End Sub
I would like to change the value in the whole row to blank if the value in column C is larger than 998.
The line is the last line of code, how do I change this?
Thanks,
YLP
Sub PriceLabels()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LRow1 As Long, LRow2 As Long
Dim r As Long
Set ws1 = Sheets("Update")
Set ws2 = Sheets("PriceLabels")
LRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
LRow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws2.Activate
ws2.Range("a2:d" & LRow2).Clear
ws2.Range("A1:D1") = Array("Item#", "Record Description", "Qty", "Price")
ws1.Range("A2:B" & LRow1).Copy ws2.Range("A2")
ws1.Range("G2:G" & LRow1).Copy ws2.Range("C2")
ws1.Range("L2:L" & LRow1).Copy ws2.Range("D2")
With ws2
.Rows("1:1").HorizontalAlignment = xlCenter
.Rows("1:1").Font.Bold = True
.Cells.Columns.AutoFit
.Rows("1:1").HorizontalAlignment = xlCenter
.Rows("1:1").Font.Bold = True
End With
With ws2.Range("A2:D" & LRow2)
.Select
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC3>998"
.FormatConditions(1).Interior.ColorIndex = 1
End With
For r = 2 To LRow2
If Cells(r, "C").Value > 998 Then Cells(r, "A").Value = ""
Next r
End Sub