Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 27th 18, 01:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 150
Default 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.





  #2   Report Post  
Old August 27th 18, 04:00 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,625
Default 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
  #3   Report Post  
Old August 28th 18, 08:53 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 150
Default Step thru Row No & Array Match

Hi Claus

Awesome as always, thank you.

Cheers
Mark.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating a Drop Down List with Step by Step Instructions for 2007 remarkable Excel Worksheet Functions 2 March 22nd 09 05:36 AM
index match array function-returning only first match, need last. Julie Olsen Excel Worksheet Functions 3 December 29th 06 01:50 AM
Index and Match - the next step MoonWeazel Excel Worksheet Functions 5 October 27th 05 05:43 PM
an array to step through sheets David Shearer Excel Programming 3 March 1st 04 10:26 AM


All times are GMT +1. The time now is 10:50 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017