Color coding cells based on value through a column with specific header

Question

I have this code in vba that defines arrays for column headers that I want copy/pasted in another tab in Excel. However, in one of the new tabs, I also want to color code some cells based on their value in the column "BOM PROCESS TYPE (A, U, R, D)" which corresponds to position 2 in that array. The code runs without giving me an error, but the cells don't change color at all. Skipping some parts, this is what I have, does anyone know how to fix it?

'My variables.

Dim i As Long, rngCell As Range, rCell As Range
Dim c As Long, v As Long, vMHDRs As Variant, vBHDRs As Variant
Dim s As Long, vNWSs As Variant, wsMM As Worksheet

vBHDRs = Array("BOM LEVEL", "BOM PROCESS TYPE (A, U, R, D)", "ALTERNATIVE ITEM: GROUP")

'Skipping most of the code and jumping to the color coding section:

With Sheets("BOM")
v = 2
Set rngCell = Sheets("BOM").UsedRange.Find(What:=vBHDRs(v), LookAt:=xlWhole)   
If Not rngCell Is Nothing Then        
Set rngCell = Intersect(Sheets("BOM").UsedRange, rngCell.EntireColumn)
    For Each rCell In rngCell
    If rCell.Value = "D" Then rCell.Interior.ColorIndex = 3
    If rCell.Value = "R" Then rCell.Interior.ColorIndex = 6
    If rCell.Value = "U" Then rCell.Interior.ColorIndex = 6
    Next
End If
End With

Any thoughts?

Liniel · Accepted Answer · 2014-12-11 12:45:02Z

2

When you use With construction, you shouldn't use Sheets("BOM"), should you?

Set rngCell = .UsedRange.Find(What:=vBHDRs(v), LookAt:=xlWhole)

answered Dec 11, 2014 at 12:45

Liniel

7291 gold badge7 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Liniel Over a year ago

Try MsgBox(rCell.Address) in for each loop. If it's null then you know where you must search :)

Mark Moore · Accepted Answer · 2014-12-11 14:30:44Z

I have just simulated your colouring code and got it working. I believe your problem is the v=2 line This is because of the way you have allocated your array and the default excel settings. The lower boundary for an array when allocated using your method above is 0, so this means you v=2 is refering to the "ALTERNATIVE ITEM: GROUP" column and so it is not finding D,R or U in that column. You can either change to V=1 (and this works) or set option base 1 at the top of your module as this will change the default lower boundary to 1. I actually advise against the option base 1 if you have multiple modules as if you forget to put option base 1 at the top of all of them, you might get unexpected results. As mentioned above, you dont need the Sheets("BOM") inside yoru With block, but it doesn't affect it working. This is the very slightly amended code that works for me

Sub test2()
Dim i As Long, rngCell As Range, rCell As Range
Dim c As Long, v As Long, vMHDRs As Variant, vBHDRs As Variant
Dim s As Long, vNWSs As Variant, wsMM As Worksheet
vBHDRs = Array("BOM LEVEL", "BOM PROCESS TYPE (A, U, R, D)", "ALTERNATIVE ITEM: GROUP")
With Sheets("BOM")
    v = 1
    Set rngCell = Sheets("BOM").UsedRange.Find(What:=vBHDRs(v), LookAt:=xlWhole)
    If Not rngCell Is Nothing Then
    Set rngCell = Intersect(Sheets("BOM").UsedRange, rngCell.EntireColumn)
        For Each rCell In rngCell
        If rCell.Value = "D" Then rCell.Interior.ColorIndex = 3
        If rCell.Value = "R" Then rCell.Interior.ColorIndex = 6
        If rCell.Value = "U" Then rCell.Interior.ColorIndex = 6
        Next
    End If
End With
End Sub

Mark Moore · Accepted Answer · 2014-12-11 13:56:12Z

0

If your target sheets and the logic around the colouring of cells is consistent, then could you not achieve your desired objective using conditional formatting on the target sheets cells. Then all you macro needs to do is the copying.

answered Dec 11, 2014 at 13:56

Mark Moore

5104 silver badges13 bronze badges

1 Comment

gssd Over a year ago

Yes, but my macro creates the new sheet as well, so I can't have the formatting on the sheet beforehand using conditional formatting.

Collectives™ on Stack Overflow

Color coding cells based on value through a column with specific header

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related