Hi Mark,

Am Tue, 8 Aug 2017 14:21:04 +0200 schrieb Claus Busch:

try:

With Sheets("TMS DATA")
Set myRng = .Range("B6:B300")

or more readable:

Sub Test()
Dim myRng As Range, c As Range
Dim myColor As Long

With Sheets("TMS DATA")
Set myRng = .Range("B6:B300")

For Each c In myRng
If c = "NEW" And c.Offset(, 23) = "N" Then
myCrit = .Cells(c.Row, 34).Value
Select Case myCrit
Case "MCDH"
myColor = RGB(204, 255, 204)
Case "MLDC"
myColor = RGB(0, 176, 240)
Case "MNDC"
myColor = RGB(192, 192, 192)
Case "MRDC"
myColor = RGB(204, 153, 255)
Case "MSDC"
myColor = RGB(255, 204, 0)
Case "MVSC"
myColor = RGB(0, 112, 192)
Case "MSSS"
myColor = RGB(112, 173, 71)
Case Else
myColor = xlNone
End Select
c.Offset(, -1).Resize(, 25).Interior.Color = myColor
End If
Next c
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016