Just the new rows?
You'll have to record a macro to find the colors you want to use--I just used 4,
3, 2, 1 (Not close to what you want--but there are lots of shades of green.)
Option Explicit
Sub Macro99()
Dim numRows As Long
Dim iRow As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim myColorIndex As Long
numRows = Application.InputBox("How many Rows", Type:=1)
If numRows < 1 Then Exit Sub
Application.ScreenUpdating = False
With ActiveSheet
FirstRow = 5 '<--
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
.Rows(iRow + 1).Resize(numRows).Insert
.Rows(iRow).Copy _
Destination:=.Rows(iRow + 1).Resize(numRows)
If IsNumeric(.Cells(iRow, "D").Value) Then
.Cells(iRow + 1, "D").Resize(numRows).Value _
= .Cells(iRow, "D").Value + 110
End If
Select Case .Cells(iRow, "W").Value
Case Is = 1: myColorIndex = 4
Case Is = 2: myColorIndex = 3
Case Is = 3: myColorIndex = 2
Case Is = 4: myColorIndex = 1
Case Else
myColorIndex = xlNone
End Select
.Cells(iRow + 1, "x").Resize(numRows).Interior.ColorIndex _
= myColorIndex
Next iRow
End With
Application.ScreenUpdating = True
End Sub
sloanranger wrote:
I have one more request,
i need to expand on the macro you wrote, this time can you make it turn
column X into a colour based on a number in column W
the colours i need are red, blue, yellow and green and the are
determined by the numbers 4,3,2 and 1 respectively.
example
X5=35mv
X6=70ml
X7=35ml
X8=70mv
dont worry about what X contain, but they must stay (they are the
result of a formula)
and
W5=4
W6=3
W7=2
W8=1
therefore X5 colorindex should be red, X6 should be blue, X7 should be
yellow and X8 should be green
and all this needs to be applied before the insert and copy loop starts
Hope you can help
Lee Sloan
--
Dave Peterson
|