Hi All

Was hoping someone could assist with the correct syntax for the following as it doesnt work for me; essentially, if the 2 conditions match, then change colour of the cells.

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

For Each c In myRng
If c = "NEW" Then
For i = 6 To 300
myCrit = Sheets("TMS DATA").Cells(i, 25).Value And Sheets("TMS DATA").Cells(i, 34).Value
Select Case myCrit
Case "N" And "MCDH"
c.Offset(, -1).Resize(, 25).Interior.ColorIndex = 35
Case "N" And "MLDC"
c.Offset(, -1).Resize(, 25).Interior.Color = 15773696
Case "N" And "MNDC"
c.Offset(, -1).Resize(, 25).Interior.ColorIndex = 15
Case "N" And "MRDC"
c.Offset(, -1).Resize(, 25).Interior.ColorIndex = 39
Case "N" And "MSDC"
c.Offset(, -1).Resize(, 25).Interior.ColorIndex = 44
Case "N", "MVSC"
c.Offset(, -1).Resize(, 25).Interior.Color = 12611584
Case "N", "MSSS"
c.Offset(, -1).Resize(, 25).Interior.ThemeColor = xlThemeColorAccent6
Case Else
MsgBox "No Match Found"
End Select
Next i
End If
Next c

TIA
Mark

Hi Mark,

Am Tue, 8 Aug 2017 04:52:17 -0700 (PDT) schrieb Living the Dream:

Was hoping someone could assist with the correct syntax for the following as it doesnt work for me; essentially, if the 2 conditions match, then change colour of the cells.

try:

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

For Each c In myRng
If c = "NEW" And c.Offset(, 23) = "N" Then
myCrit = Sheets("TMS DATA").Cells(c.Row, 34).Value
Select Case myCrit
Case "MCDH"
c.Offset(, -1).Resize(, 25).Interior.ColorIndex = 35
Case "MLDC"
c.Offset(, -1).Resize(, 25).Interior.Color = 15773696
Case "MNDC"
c.Offset(, -1).Resize(, 25).Interior.ColorIndex = 15
Case "MRDC"
c.Offset(, -1).Resize(, 25).Interior.ColorIndex = 39
Case "MSDC"
c.Offset(, -1).Resize(, 25).Interior.ColorIndex = 44
Case "MVSC"
c.Offset(, -1).Resize(, 25).Interior.Color = 12611584
Case "MSSS"
c.Offset(, -1).Resize(, 25).Interior.ThemeColor = xlThemeColorAccent6
End Select
End If
Next c
End With


Regards
Claus B.
--
Windows10
Office 2016

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

Hi Claus

Once again, you have come to the rescue and save me banging my head on the desk.. :)

Thank you.

Cheers
Mark.