Step thru Row No & Array Match
 

Hi All

(Claus)! if you're reading this, I butchered this from one of the codes you did for me, and in-as-much as it runs through and doesnt throw an error, it doesn't do anything either.

This is my existing code, which works just fine, but I'm trying to do two things he 1. update it to something quicker, less relient on If's and Selects & 2. Improve my coding.

Existing:

Sub Repaint()
Dim myRng As Range, c As Range
Set myRng = Sheets("Master").Range("G5:G300")
For Each c In myRng
If Not c < "" Then Exit Sub
If Not c = "" Then
Select Case True
Case c.Value = "HDC"
c.Offset(, -6).Resize(, 15).Interior.Color = 65535
Case c.Value = "LDC"
c.Offset(, -6).Resize(, 15).Interior.Color = 15773696
Case c.Value = "NDC"
c.Offset(, -6).Resize(, 15).Interior.ColorIndex = 15
Case c.Value = "RDC"
c.Offset(, -6).Resize(, 15).Interior.ColorIndex = 35
Case c.Value = "SDC"
c.Offset(, -6).Resize(, 15).Interior.ColorIndex = 44
Case c.Value = "SSL"
c.Offset(, -6).Resize(, 15).Interior.ColorIndex = 44
End Select
End If
Next
End Sub

The New Attempt: ( Any pointers on where I went wrong would be awesome ).

Sub Color_Me()
Dim Color1 As Long, Color2 As Long, Color3 As Long, Color4 As Long, Color5 As Long, Color6 As Long, Color7 As Long
Dim varDC As Variant, varColor As Variant
Dim L_Row As Long
Dim i As Integer

DC_1 = "HDC"
DC_2 = "LDC"
DC_3 = "NDC"
DC_4 = "RDC"
DC_5 = "SDC"
DC_6 = "SSL"
DC_7 = "VPS"

varDC = Array(DC_1, DC_2, DC_3, DC_4, DC_5, DC_6, DC_7)

Color1 = RGB(255, 255, 0): Color2 = RGB(51, 204, 255)
Color3 = RGB(153, 153, 153): Color4 = RGB(153, 255, 204)
Color5 = RGB(255, 102, 0): Color6 = RGB(255, 102, 255)
Color7 = RGB(51, 51, 153)

varColor = Array(Color1, Color2, Color3, Color4, Color5, Color6, Color7)

With ActiveSheet
L_Row = .Cells(Rows.Count, "G").End(xlUp).Row
End With

For i = 5 To L_Row Step -1
i = Application.Match(varDC(i), L_Row)
Cells(i, 1).Resize.Cells(i, 14).Interior.Color = varColor(i)
Next

End Sub

As always
Many thanks in advance.

Cheers
Mark.

Step thru Row No & Array Match
 

Hi Mark,

Am Mon, 27 Aug 2018 05:56:26 -0700 (PDT) schrieb Living the Dream:

Sub Color_Me()

try:

Sub Color_Me()
Dim Color1 As Long, Color2 As Long, Color3 As Long, Color4 As Long, Color5 As Long, Color6 As Long, Color7 As Long
Dim varDC As Variant, varColor As Variant
Dim L_Row As Long
Dim i As Integer

Const myStr = "HDC,LDC,NDC,RDC,SDC,SSl,VPS"

varDC = Split(myStr, ",")

Color1 = RGB(255, 255, 0): Color2 = RGB(51, 204, 255)
Color3 = RGB(153, 153, 153): Color4 = RGB(153, 255, 204)
Color5 = RGB(255, 102, 0): Color6 = RGB(255, 102, 255)
Color7 = RGB(51, 51, 153)

varColor = Array(Color1, Color2, Color3, Color4, Color5, Color6, Color7)

With ActiveSheet
L_Row = .Cells(.Rows.Count, "O").End(xlUp).Row
For i = LBound(varDC) To UBound(varDC)
If Application.CountIf(.Range("F2:F" & L_Row), varDC(i)) 0 Then
.Range("A1:O" & L_Row).AutoFilter , field:=6,
Criteria1:=varDC(i)
.Range("A2:O" &
L_Row).SpecialCells(xlCellTypeVisible).Interior.Co lor = varColor(i)
.Range("A1:O" & L_Row).AutoFilter field:=6
End If
Next
End With

End Sub


Regards
Claus B.
--
Windows10
Office 2016

Step thru Row No & Array Match
 

Hi Claus

Awesome as always, thank you.

Cheers
Mark.